Query for Multiple Lookups

  • Thread starter Thread starter Rita_M
  • Start date Start date
R

Rita_M

I'm using Access as the reporting tool for a third-party-developed
proprietary database (I can't change the structure of the database). The
database has a "master" lookup table that includes every type of lookup value
for the entire database (not separate lookup tables for State code,
employment status, practitioner category, etc.)

My query joins the code from the first field to the code field in the master
lookup table and properly displays the description. When I attempt to join a
subsequent field to another instance of the master lookup table (using same
type of join as first lookup), the second instance of the lookup becomes
"lookup_1", but then gives a "Type Mismatch in Expression" error message. My
work around has been to copy the master lookup table and rename the copies
for as many instances as I need in my query, and then use those renamed
copies to do the joins. But I'd have to recopy those extra instances each
time I run my query to be sure I have the current data.

Any thoughts how to do this more efficiently? Thanks.
Rita
 
Rita

If this master lookup table has lookup values for more than one type of
variable (e.g., numeric, text, date/time, ...), you might get a type
mismatch error if you were trying to connect to it with a variable type that
didn't match the variable type in the lookup table. ?!?!Wouldn't that be
most of them?

What am I missing?

(If this is what's happening, you might be able to work-around the master
lookup table by creating as many queries as it takes to isolate each type of
lookup. That is, from your example, you would set up a qlkpStates (for
state codes), a qlkpEmploymentStatus (for employment status), etc.

Then you could use those "lookup queries" instead of multiple copies of the
master...

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
When I attempt to join a subsequent field to another instance of the master
lookup table (using same type of join as first lookup), the second instance
of the lookup becomes "lookup_1", but then gives a "Type Mismatch in
Expression" error message.
There should not be a problem. Post the above query SQL. Also post the
datatype of the fields involved in the join with sample data.

Instead of coping the table for second instance did you try using a select
query?
 
Back
Top