queries in access

A query is a question to the database.
"Who is the best sales rep this month?"
"How much money did I make last month?"
"How many widgets do we have in stock?"

In all database applications queries are used to extract information from tables and display it like a table.

A query looks like a table and is treated exactly the same as a table by Access. In fact, a query is sometimes called a dynamic table.

The really big difference between the two is that the data in the query may come from 1 table or 3 tables or 12 tables and will be selected based on criteria.

a new application

To our students who started the Access 2007 course and noticed that it was never finished, we apologize.

All is not lost, however. We'll continue the course here.

If you're working with the Access 2007 software still, you will have no difficulty in applying these lessons to your work. The differences between the two versions are so minor that you won't even notice.

Even if you're looking at these tutorials from Access 2003 you should have no problem applying the following lessons on queries, joins, VBA code and functions. All these topics are essentially the same in all versions of Access.

For the rest of these lessons we'll use examples and exercices from both applications: Profsr Financial Services and Mike's Garage.

To catch up, study the lessons from the Access 2007 course and the download the sample database.

Access 2007 course content

Mike's Garage sample database

a selection query

Let's look at the first question above, for example. "Who is the best sales rep this month?". Seems easy enough.

Assuming that "best" means the greatest amount in sales, we will need every person's name from the Rep table, the amount for every sale from the Sales table but, selecting only sales that are this month. Then we must get a total for every sales rep and sort all the totals into some kind of order to be able to compare the results.

Turns out to be a little bit more complicated than it looked, doesn't it!

Before we tackle that one, let's look at a few simple queries from both applications.

In the Financial app, who are the reps? In other words: give me a list of reps.

Produce a selection query, run it, then save it calling it "ListReps" so that you can produce the list instantly at any time by just running the query and this is what you get:

Now, let's look at another simple query, this time with selection criteria.

In Mike's Garage, list all the customers who are women.

Produce a Selection query with the criteria that the Sex column contains F (at design time we decided to use M for Male and F for Female). Then test it, save it with the name "ListWomen" and that's it.

Let's get a bit more sophisticated. We'll build a "Join query", meaning we'll join the information from 2 tables.

In Mike's Garage, the client is actually a vehicle. That's just to simplify things because a vehicle always has only one owner (a customer), but a customer can have several vehicles. So, we designed it so that in the Vehicles table we identify the owner, rather than the other way around.

Here's a fairly simple Join query: Give me a list of all the vehicles (make, model and year) with the owner's name and email address.

Sometimes you have to be inventive to find criteria. The person asking the question doesn't always (actually, it's never) use Access terms to describe the query.

The request is: "Give me a list, in order of decreasing value, of all the sales where there was more than one rep involved."

How do you find the ones with more than one rep? Think! If the percentage of the sale to the rep is 100%, he was working alone (or the other guy worked for free, which is the same thing).

So the query would be:

Save the query as: SalesByMultipleReps

The queries we just looked at did not involve any dates. Working with dates will introduce new problems and we'll look at those over the next few lessons.