Many times people will make the mistake of using lookups at table level instead of using them only at a form level. One of the big problems with this is that it obscures the way lookups actually work. The idea of a lookup is that you have a value and you lookup an associated datum that goes with it. So, for example, I might have a table which stores a client ID of 4. This ID of 4 refers exclusively (primary key) to a row within the Clients table.

This would let me go look in the ID field to find the associated name that goes with that ID of 4. So, let’s say that the client that is associated with an ID of 4 is named “Big Top Tires.” I may want to display “Big Top Tires” in my form or report but, because of normalization principles, I only store the ID of 4 (now a foreign key in another table) so that it will always uniquely identify that record to be associated with that client. But, let’s say that I actually found out that I had typed in “Big Top Tres” in the client table. That is not really a problem, IF we have been storing the primary key associated with that name. However, if we had been storing the text “Big Top Tres” for each record in our transactions table (for example), we would have to go change potentially hundreds, if not thousands, of records to correct the error. If, however, we have been storing the ID associated with this company then we would only have to change the name in ONE place – the client table. That is one reason why this type of data storage is used.

So, many people are unfamiliar with queries and the benefits of using them over using tables in their forms and reports. They think that they need to use a table as the underlying record source in a form or report. This is definitely not true. You can and, I would argue, you should use a query as the underlying record source in a form or report. Queries can be treated just as tables can.

So, how do you go about using a query as a record source, especially if you have lookups that you want to show the associated text instead of the ID number? That is what we will go into right now and we will use a report as the example.

First, we will create the base of the report. I find that it is quite easy to use the Wizard to create the base report and then modify it to fit the needs instead of trying to create it all from scratch. For our example we will use the Northwind 2007 database. The Northwind database, unfortunately uses lookups at table level (we’ve tried to get them to stop that practice) so I have removed those in order to see what you would normally see if you didn’t use them (which would be correct).

We will start with creating a report from the Order Details table which has lookups (OrderID, Product, and Status).

We will then start creating a report using the report wizard.

So, as you can see, you can easily make use of queries to present what you may have previously though inaccessible. This is a great way to store the data and present it.