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.