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.

   

Click Create query

 

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

   

Build query

 

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

Select tables

 

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.

   

Select fields

 

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

Build Query

 

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.

   

Save Query

 

Enter query name:

Name query

 

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

Design View

Select View from main menu and SQL View

SQL View

You can see actual query:

Query text

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 fields The 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<< 

Please rate the tutorial

1 2 3 4 5 6 7 8 9 10



Learn SQL Programming By Examples [Kindle Edition]2.99

Learn PHP Programming by Examples [Kindle Edition] $2.99

Learn Visual Basic 6.0 [Kindle Edition] $1.99

How to Build Your Own Web Site from Scratch [Kindle Edition] $1.49

New-trip.com website source code

Comments
 
Register to add comments ( 1000 char ) for dbconcept3.php.