How to get Started Using Microsoft Access Even if You've Never Used it Before by Paul Barnett - HTML preview

CREATE A QUERY

We can use a query to get different views of data. Go back to the database window and select the ‘Query’ tab. Click the ‘New’ button and select ‘Design View’. You will now see a list of tables we created earlier.

 

Select each table and click the ‘Add’ button.

 

Each table will appear in the query designer. Click the ‘Close’ button when you are done.

You will notice that the tables are linked via CustomerID. This is because once again Access recognises fields with identical names and assumes it is the primary and foreign key.

In the customers table double click the FirstName and LastName fields. You will notice these fields appear in the grid. Double click the OrderDate, ProductNumber and Price fields from the Sales table. These will also appear in the grid.

Click the Run button at the top menu

Depending on the number of records you added to the system you will see something similar to the following:

We can see for example that John Smith made 3 purchases.

 

Although this gives us a quick overview we can also get a more useful picture by grouping data.

 

Click the design button

 

on the menu. This takes us back to the query designer.

 

Remove the OrderDate field by clicking the bar above the name until the column highlights.

 

Now press the ‘Delete’ key on your keyboard

 

Do the same with the ‘Product Number’ field

You should now be left with a display as follows:

We now want to group the names and sum so that we can get an overall total for each person.

On the menu bar click the grouping button

 

Your grid will now show

 

We can see that all fields now have a grouping. We want to sum the ‘Price’ column though so click into the ‘Total’ box for the price and change it’s Group By to Sum.

 

You should now have

 

Now click the Run button

 

on the top menu

 

We can now see that the data has been grouped for each person and we get a running total of what they have purchased.

 

If you only want to see totals for individuals you can do this also.

 

Go back to the design view of the query and enter the first and last name into the grid.

 

You don’t need to enter the quotation marks as MS Access will automatically do this.

 

Click the ‘Run’ button and you should see and individual total record

 

If you now save your query you can create a report based on it. Queries are a very powerful feature of MS Access and are great for reports.

 

I hope I have given you some useful tips on how to get started with MS Access and what can be achieved. I cover things in greater detail in further ebooks and videos.

 

Access is a very powerful database tool and one that can take your business forward.