Part III: Creating Database Query in Microsoft Access

By Sergey Skudaev

Let us continue learning how to create Microsoft Access database and queries and how to select, update or delete records in Microsoft Access databas tables.

Select Query from object panel and double click Create by Design View.

Show table window displays. Select a table and click the Add button.

Table displays in the Select Query window. Repeat procedure for the next table and click the Close button. Now you can select fields that you want to display in the record set.

If you want the column to be hidden do not mark the Show check box.

When you select all desired fields, click the run button ( ! )
Record set displays in the Select Query window. Click the X in the upper right corner of the Select Query window. System prompts you to save query. Click Yes.

Enter query name:

and click OK. Select your query and double click Design View

Select View from main menu and SQL View

You can see actual query:

SELECT credit_cards.name, credit_cards.type, credit_cards.expired, transactions.balance, transactions.min_payment, transactions.due_date, transactions.paid_date, transactions.paid_amount, transactions.check_number
FROM
credit_cards INNER JOIN transactions
ON credit_cards.cardid = transactions.cardid;

Query anatomy:

After the word SELECT there goes lists of field names with table name connected with dot, then goes FROM and list of the tables from which the fields are taken. Then goes clause that determined relation between records in listed tables

This query may be written this way:

SELECT * FROM credit_cards
INNER JOIN transactions
ON credit_cards.cardid = transactions.cardid;

* stay for all existing fieldsThe other way to write query:

SELECT * FROM credit_cards, transactions
WHERE
credit_cards.cardid = transactions.cardid;

Query to select transactions that belong to Bank One credit card:

SELECT * FROM credit_cards, transactions
WHERE
credit_cards.cardid = transactions.cardid and credid_card.name='Bank One';

Query to select transaction between two dates:

SELECT * FROM credit_cards, transactions
WHERE
credit_cards.cardid = transactions.cardid and transactions.due_date between #12/01/2004# and #01/20/2005#

The same result you can get using a query:

SELECT * FROM credit_cards, transactions
WHERE
credit_cards.cardid = transactions.cardid and
transactions.due_date > #11/30/2004# and transactions.due_date < #01/21/2005#

 PART I <<   PART II<< 

My eBooks on Amazon.com