As I have mentioned before, Ms Access query is considered the most important object in your database. It’s the heart of the system and if you bypass it, there’s simply no life to your application!
Just to put words into pictures, have a look at this simple diagram below (taken from one of my eBooks)
You can see the above ‘Middle’ tier is a query which sits comfortably in the middle servicing all other key ‘Access‘ objects. Now imagine there were no queries! How inflexible would your tables, forms and reports be?
The query brings together the main source files (tables) which are compiled and churned out into some sensible arrangement of records (the results) that can include complex joins, in depth calculations and strong criteria and without this you have very little to offer.
Microsoft Access Queries: 7 Basic Things to Know About MS Access Query
So, those of you who are new to Microsoft Access and the general database design concepts, here are 7 basic things to know about MS Access queries.
1. Queries are like Tables but the key difference here is one physically stores the data; the other is a view of the data.
2. The simple SELECT queries are safe to run (or execute) because they do not change values but instead views the information from tables and other queries. With more complex queries, the data is also locked preventing values being edited too.
3. All queries are written as a SQL (Structured Query Language) query which is the standard back-bone language for most database applications. You can opt to write via this view and in some cases is the only view for the more advanced query. When you save a Ms Access query, this is what is being stored – the SQL statement (as text in a file but with no data).
4. Be careful with the Action queries which are potentially dangerous. There are four in all and can change existing values to your tables or duplicate records elsewhere like the Make-Table query. The most dangerous query of all – The DELETE query!
5. To join or not to join? Joins are absolutely necessary if you are using multiple tables in queries. This is what Microsoft Access queries are really about; the use of RDBMS (Relational Database Management Systems). If you have a table (or query) sitting alone in the corner being anti-social, then it will have a big impact on the rest. There are some rare occasions for a table to not be joined with other tables but generally they should all be joined.
6. You must specify criteria for your query otherwise what’s the point? All records would be shown in the query as per your table(s). The idea is to increase the general usability and performance and optimise records to only the information really required. Using the logic conditions of AND and OR will in most cases reduce the record results.
7. Datasheet view versus Run view do vary based on the type of query you are running. The datasheet view is the safe mode, preview mode and allows all records to be shown under its sorting, criteria and calculations. The only time the Run view mode (the red exclamation icon) varies will be for the potential Action queries which will action any changes (hopefully not before you see a warning).
There are other reasons too but the above should start to separate out why we use Microsoft Access queries at all.
If you are completely new to queries, then check out the couple of links below:
How To Create an Access Query – Part 1
How To Create an Access Query – Part 2
Here’s a final tip! An Access query is based on one or more tables or queries so it can get very busy indeed and in some cases confusing to administer. You may want to consider splitting the ‘Middle‘ tier into two internal tiers; ‘Upper Middle‘ and ‘Lower Middle‘ where the first could be a collection of base queries for merging tables together, adding standard sorting and basic calculations to be used globally. The latter could then be adapted for the more individual query or report based on one of the global queries instead keeping designs easy to follow and more manageable. This is sometime referred to as ‘Nested‘ queries.
Tags: access query design, action queries, microsoft access queries, microsoft access query criteria, MS Access Query, writing queries in ms access