In part 1, I explained the worklfow and planning on how to create an Access query by taking a pen and paper exercise to create that checklist. In this second part, I’m going to quickly step you through the essentials.
As a reminder, here’s the pseudo statement that started the process to list your tables, fields and other key attributes including criteria and sorting:
Show a list of order transactions for the first quarter of 2011 of all customers based in the UK showing the date, order number, amount, delivery cost and itemised in order by customer name and chronologically by date for each customer.
How To Create An Access Query: The Simple Steps (Part 2)
Here are the steps (I’m using Microsoft Access 2010):
- Create an Access query by going to the Create tab, choosing Query Design icon.
- Choose the tables for your query; in this case we have two (Customers and Orders).
- Add the fields using your checklist in the order they are to appear (left to right).
- Quickly run the query to make sure it’s working – back to design view when done.
- Add criteria one by one and test each addition including any sorting options too.
- Finally check th results and make sure there are no missing or duplicated records.
- Save changes – done!
The criteria for the first quarter of 2011 would look something like:
Between #01/01/2011# And #31/03/2011#
The criteria to show only the UK based customers would look something like:
To learn more about how to really understand how to create an Access query, my eBook on How to Build Access Database Queries – using easy to understand principles will cover all the essentails and comes with a 100% money back guarantee.
Tags: create an access query, how to create a query, microsoft access query builder, query in ms access, setting criteria in queries
If you wantedto write the above example as an SQL query instead, it may look somthing like:
SELECT Customers.[Company Name], Customers.Country, Orders.[Order ID], Orders.[Order Date], Orders.[Order Amount], Orders.Freight
FROM Customers INNER JOIN Orders ON Customers.[Customer ID] = Orders.[Customer ID]
WHERE (((Customers.Country)=”UK”) AND ((Orders.[Order Date]) Between #1/1/2011# And #3/31/2011#))
ORDER BY Customers.[Company Name], Orders.[Order Date];
This assumes that your two tables are called Customers and Orders.