Creating An Access Query: The Simple 3 Step Planning Approach On How To Create An Access Query
Being a qualified Access database trainer and recognised as an accredited IT training in the UK with the Institute of Information training technologies (IITT), delivering Microsoft Access training courses can be sometimes a little ‘dry’ and in some instances quite complex based on the level of expertise required.
Teaching new users to use Microsoft Access and especially how to create Access queries requires a little more patience and understanding with the tools that are available and the emphasis behind using Access queries can’t be stressed enough.
The methods in explaining and using how to create an Access queries starts with the first process of planning your query and that means a pen and paper exercise (or using a text editor).
Here are my 3 steps in planning how to create an Access query and how I help to break down and build on your queries using the following as a checklist:
1. Write a paragraph (or two) of the requirement for your query in plain English (or your native language) which will form the basis for your query. It can be an instruction as detailed as possible to form the basis as the checklist to the query. For example, you could write something like:
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.
2. The next step is to list all the tables and fields that will be used to display the query and used to filter and sort the query too using the written paragraph as the checklist. For example:
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.
3. The final step is to identify both sorting and the criteria and this may need additional fields (fields that you might not display) to be added to the list. It may include which order you want to sort by and if there is more than one field too as well as which criteria and conventions will be needed to apply logical filters. For example
Country = “UK” and Order Date = Between #01/01/2011# And #31/03/2011#
Customer Name = ASC, then by Order Date = ASC
Finally, revisit the written paragraph and mark out that you have covered all the elements as part of the check list.
Now you are ready to create an Access query and this is where you go into the application and apply the above attributes.
achsan uddin