How To Create An Access Query: The Simple Steps (Part 2)

How To Create An Access Query: The Simple Steps (Part 2)

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)

create an access query gridHere are the steps (I’m using Microsoft Access 2010):

  1. Create an Access query by going to the Create tab, choosing Query Design icon.
  2. Choose the tables for your query; in this case we have two (Customers and Orders).
  3. Add the fields using your checklist in the order they are to appear (left to right).
  4. Quickly run the query to make sure it’s working – back to design view when done.
  5. Add criteria one by one and test each addition including any sorting options too.
  6. Finally check th results and make sure there are no missing or duplicated records.
  7. 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:

= "UK"

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.

This entry was posted in MS Access, Queries, Utilities and tagged , , , , . Bookmark the permalink.

One Response to How To Create An Access Query: The Simple Steps (Part 2)

  1. Ben Beitler says:

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>