In this post, I’m going to talk about the planning of how to create an Access query, which is the step prior to using Microsoft Access itself (that’s in part 2).
The Access query is an important object to your database as it will serve you in your final reports, maintain data integrities and control workflows with code.
How To Create An Access Query: The Simple Steps (Part 1)
Here are my steps to planning and create an Access query focusing first on the planning of such an object:
If this query is to serve as a report whether it is going to be exported, published on the web or used in an Access report, you need to decide on what fields (columns) of information you will need. But the first thing I teach my students is to write down on piece of paper in a sentence or two the report requirements.
This should be clear and simple to understand! For example, take a look at the following sentence:
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.
You will need to know the architecture of the database i.e. which tables and fields this information will be used to supply the above request and this is the next step by listing the tables and fields:
Table: Customers - fields: name and country Table: Orders - fields: order number, date, amount, delivery cost Also identify where there is a common link between the two tables; in this case 'customer reference' will be stored in both tables.
Next step is to list the criteria and sorting options by going back to the sentence and re-read it:
- Sort by the customer name and then by the order date (in that order).
- Set criteria to the country origin (in this case UK) and apply a rule to return the first three months of 2011 from the order date field.
Using the sentence, you highlight and cross out your words one by one meeting the objectives above – use it as the checklist.
Now you are ready to create an Access query in Microsoft Access which is covered in part 2 – Keep a look out for it!
In the meantime, you can always consider my eBooks that step you through the process of building Access queries (the correct way). Perhaps you may want to share your method of how to create an Access query in the reply box below 🙂