Microsoft Access Queries: Some Pointers To Building Those Reports

Microsoft Access Queries: Some Pointers To Building Those Reports

Database developers have admired Microsoft Access because it’s scalable and user friendly (for a development application that is) and provides a good ‘one-stop‘ shop balance for database management meeting small and medium sized company needs.

I have always harked on about the key to a good database is in the query and just in case you are not yet familiar with Microsoft Access queries, here is just a little bit of information.

What are Access Queries?
Well the simple answer is…

"Access, I have a question for you!"

…It then goes off and finds the answer.

In other words, a query is simply an instruction or request for a report or some other form of output of information.

The language it uses to communicate and ask that question of your data is known as SQL (Structured Query Language) and it uses this SQL query syntax to provide answers very quickly indeed.

microsoft access queries some pointers

The advantage with Microsoft Access database queries however, is that there is no requirement to learn the language at all as it provides a user-friendly screen interface known as QBE (Query By Example grid).

Microsoft Access Queries: Some Pointers To Building Those Reports

So now we know that queries are requests for your data that yield answers. There are so many variations of queries ranging from the simply SELECT query to multiple joins and nested SQL based queries that it will take some time to master them all.

Here are some points to consider that some queries may be extremely complex when summing, calculating and grouping of information.

1. Know your data (and types) – This will help to assist you in identifying the qualifiers that you could use, you need to know the data types for every field for your Microsoft Access database that will likely end up as an Access report. Make a note of each field and their data types (Text, Number or Date).

2. Know your database structure – In order to be certain that that you have every single one of the fields desired, write down or document (electronically) all the names of the tables and their fields that you would like on your main query report.  Use this as your checklist and refine the list.

3. Design, Test and Repeat – Remember, at any time querying, you can actually add or remove fields first until you have the correct listing show all records to help validate the results even before applying criteria. This way, the test query will allow you to get the entire number of records being listed and not fail to run.

4. Build and Refine On Step at a Time – You may then want (and should) add some criteria option one after the other. This would save you valuable time should you come across any error – as it is possible to pinpoint the cause quickly.

Of course, I’ve written many articles about queries and there’s at least one other useful post to also check out  Microsoft Access Queries: 7 Basic Things to Know About MS Access Queries

As I have mentioned before, Microsoft Access queries 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)

Leave a Reply

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