Queries For MS Access 2000, 2002, 2003, 2007

Click to see the first tutorial on MS Access Queries

In the first tutorial we covered a basic Select Query and a basic table. In this tutorial we will cover another Select query in which we will use one table and hook it to itself. Pretty much all versions from Access 97 to 2007 the query builder looks the same. In Access 2007 there are some differences. We will again be dealing with Access 2000-2003. Go here for MS Access Queries 2 in PDF Format

Assuming you have a table called CUSTOMERS and Fields named CNT(datatype AUTONUMBER), NAME(datatype Text) , ADDRESS(datatype Text) , CITY(datatype Text) , STATE(datatype Text) , ZIP(datatype Text). In design view for the table Right Click the field CNT and set it to Primary Key. We will now go through designing our new query in MS Access.

Be advised we could get the same data I have chosen to go after with out joining a second rendition of our table. This is merely an exorcise to show you how it could be done.

To design the query using the query builder simply click "Objects" in your left hand menu and then "Queries".

You will be given a choice between "Create query in Design view" and "Create query by using wizard".

This article will only deal with "Create query in Design view". Now double click "Create query in Design view". You will immediately be presented with a window called "Show Table", in it will be your table named CUSTOMERS. Click ADD Twice and then Close. MS Access will automatically join two tables by the Primary Key. This only happens when the field which is the primary key is of the same name and same datatype and the tables are not the same. Our CNT field will be our Primary Key but today we will not be joining them as we have no need for it. Its use would be for a unique identifier related to the customer. This Identifier could then be used to track those customers’ activities such as products they buy from another table. If the Primary Keys are of different names and or you are using one table twice you must manually join them by clicking and dragging one to the other. Join the "ZIP" and "NAME" fields now. Click and hold the field "STATE" from table MS Access Table Named CUSTOMERS_1 and drag it to the area below the tables and release, do the same for the "ZIP" field. You can accomplish the same result by Double clicking the fields you want. In the next empty slot type the following exactly in the top:

Count Customers: ""

Just below the "STATE column type in NH or one of the states in your own table. Now from the Menu click "View" and then Totals. In each of our columns a new row will appear called "Totals" each row will be set to "Group By" in our column Count Customers: "" use the drop down selection of the "Totals" row and set it to "Count".
We are now ready to run our query and see how many Customers are in our chosen state. The following image shows the Totals button, an alternative to clicking View and then Totals from the menu.

MS Access Query Design View, joined by two fields.

Now your query should look like the one above. Double check it now. Select queries, when ran, will not modify your data by themselves. They will allow you to view your data and in some cases to MANUALY edit it if you so choose.. Our current query where it has the "Totals" enabled will not allow us to modify our data. Note that there really was no need to join our query to itself to obtain the information I chose to obtain. This is merely an exorcise to show you how it can be done should you have need to do so. In some cases there is need however this query could be run without a second rendition of our table just fine.

We should be all set now to run our query. Click the Red "!" to run the Query.

Your view should be something like this. It is essentially a Basic Count Query. Be aware if we had only joined the "ZIP" field our Count would be double as it would count each records occurrence within both tables.

For further help or suggestions go here: MS Access tutorials, Tables, Queries and later: Forms

You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.

Click to see the first tutorial on MS Access Queries

Click here to see the MS Access Append Query Tutorial

For further MS Access tutorials go here: MS Access tutorials

Did you find what you were looking for? What would you suggest? mail@eraserve.com


Your Name:
Your Message:
Your Email:

Custom Search