more access queries

In previous lessons we looked briefly at Join queries to extract information from more than one table.

That's a very common operation. It's important to understand it. When building queries it is not unusual to have to work with 5 or 6 or more different tables and sometimes even with other queries (remember that a query is treated like a table, so you can build a query on 4 tables and 1 query, for example).

Tables in a database are like sets in mathematics. A join query is an intersection of sets - the information returned by the query has to be present in both tables to be valid.

There's a lot of theory behind relational databases and queries! But it works - 100% of the time - if you do it right. We'll look at a few examples of different queries to see what all that means.

the standard join query

Let's look again at the basic sales query from the Financial database.

"What sales were made yesterday?"

For every sale there has to be a record (row) in the SalesM table, at least one record in the SalesD table, a Rep record and a Product record. A valid sales transaction is an intersection of those 4 tables. If, for example, the rep number is not valid in the SalesD table, there is no transaction because you cannot have a sale that wasn't sold by a sales rep. The same with the product code - if the code wasn't in the Products table, there is no transaction because you can't sell a product that you don't have.

So, our query looked like this:

basic sales query

Here's another view of the same query. When you use the grid layout for an Access query, that's just a visual aid. Behind the grid Access generates the query in SQL code. If you want to skip the grid, you can always write the query directly in SQL. Or you can modify the code directly to change the way the query operates. You'll have to learn SQL eventually when you work with more complex databases.

sales query sql

All this applies to the other databases too. In Mike's Garage, to get an accurate sales report we need to use: Invoice, Invoice details, Vehicles, Customers and Parts, all intersecting one another. We can't sell to customers we don't have (who's going to pay the bill?) for vehicles that don't exist and we can't sell parts we don't have (at least not if we're honest!).

another kind of join query

The standard Join query is fine when you want to look at information you have. But what if you need information you don't have? The standard query will list the sales that were made. But what if I need to see the sales that weren't made?

I have 50 sales reps who generate a few hundred transactions every month. It's not obvious in the Sales Report if there are 3 or 4 or 12 who didn't produce any sales because they just don't show up. I need to see those names.

We need a new type of join. It's called a Left Join.

It works like his: join 2 tables and show me all the records in the left table plus all the records that intersect.

To produce our sales report for all sales reps we create a new query we'll call "LeftRep".

When we run the query we get this:

And then, to produce a more accurate Sales Report, one that shows the non-productive people, we would modify the basic sales query by using our new LeftRep query instead of the two individual tables:

And note that you sometimes have to tweak the SQL code to make it work. Access doesn't always evaluate the complex expressions properly. In this case we have had to add parentheses to the expression.

The result works - we get the sales and the no-sales. Query result was sorted on Percent field to show blanks at the beginning.

Now we have queries that will extract the information we need, in different circumstances. We can answer a lot of questions about what is happening and even what did not happen.

Next we'll look at how we can do calculations in the query using functions.