Using a Lookup Table in a Query

[John Bigness]

I have a Lookup Table that has 2 text fields - Code and DeptName. There are
100 entries in the table like - Code = "001", DeptName = "Accounting", etc.
In the Main table there are 10 fields called Code1, Code2, ...Code10. Each
of these has a 3 digit entry like "001", "002", etc followed by its
corresponding DeptName.
What we want is to build a query from the Main table to show each CodeX and
its corresponding DeptName. Problem is you can't just link the two tables
because you have the Code field more than once in the Main table. The query
and its results might look like this:
Code1 Name1 Code2 Name2 Code3 Name3
001 Acctg 004 Maint 001 Acctg

Since there is more than one Code field in the Main table I keep getting an
ambiguous error message. I also tried a DLookup function but couldn't get it
to work.
This should be easy to do. It's very easy in Excel using a VLookup function.

 

[Duane Hookom]

Your table structure seems un-normalized which causes issues like this. You
can add 10 copies of the lookup table to your query and join each code field
to each of your 10 Code# fields. You must specify your fields/columns in
your query grid like:
[Lookup Table_x].[DeptName]
where "x" is the number given to the copy of the lookup table.

 

[Ken Sheridan]

To expand on Duane's observation regarding the design of your main table, you
should seriously consider rectifying the design flaw. What you have is a
many-to-many relationship between the two current tables. This should be
modelled by a new third table with two columns, each a foreign key
referencing the primary keys of your current tables, so one column would be
Code and the other whatever the primary key of the main table is called.

With this new table if a row in the main table references 5 rows in the
'lookup' table, i.e. has values in 5 of the code columns, there would be 5
rows in the new table with the same value in the column referencing the key
of the main table and 5 separate code values.

Populating this new table is not as difficult as you might think. Just
create 10 append queries which join the main table to the 'lookup' table on
each of the code columns and append the primary key fro the main table and
the code column from the 'lookup' table into the new table. Once you are
satisfied that its been populated correctly you can delete the code columns
from the main table.

Its then a simple task of joining the three tables in a query to get the
result you want.

Ken Sheridan
Stafford, England

 

[John Bigness]

I miswrote originally - the Main table has only the Codes - Code1, Code 2,
etc.. There are no DeptNames in the Main table. The Dept table is a Lookup
table listing each Code with its corresponding DeptName.
In my query, they want each Code followed by the DeptName.
So each of these fields Code1, Code2, etc are Primary Keys in the Main table
and the Code field is the Primary Key in the Dept table.
Am I right so far? The third table Ken mentioned I'm still thinking about
so if you could reiterate an explanation it would be appreciated.

 

[Klatuu]

I believe the design is even more flawed than that. I actually have a hard
time thinking of a situation where one main record would require up to 10
departments be associated with it. Perhaps the main table needs some
rethinking.

 

[John Vinson]

I miswrote originally - the Main table has only the Codes - Code1, Code 2,
etc.. There are no DeptNames in the Main table. The Dept table is a Lookup
table listing each Code with its corresponding DeptName.

Then your table design IS WRONG.

If you have ten Codes in your main table, you're embedding a
one-to-many relationship in each record. There might be one case in a
thousand where this would be appropriate - but it almost certainly
isn't.

What Entity does your main table represent?

In my query, they want each Code followed by the DeptName.
So each of these fields Code1, Code2, etc are Primary Keys in the Main table
and the Code field is the Primary Key in the Dept table.

Are you saying that the main table has a ten-field composite primary
key? Again ... what is the PURPOSE of this table? The only way this
would make sense is if you want to establish some kind of vector
relationship amongst the ten departments...

Am I right so far? The third table Ken mentioned I'm still thinking about
so if you could reiterate an explanation it would be appreciated.

John W. Vinson[MVP]

 

[Ken Sheridan]

My previous comments still apply. There surely must be some other
columns(s) in the main table other than the Code columns or it serves no
purpose. A table represents an entity type. Your Deptartments table for
instance represents the Departments entity type and each department is an
instance of that entity type. That table is fine.

The main table must represent another entity type, which in your case has a
many-to-many relationship with Departments. Lets say for example that the
main table is PefortmanceReviews and has a primary key PerformanceReviewID
and along with other columns which represent other attributes such as the
type of review. The third table which models the relationship with
Departments would have two foreign key columns PerormanceReviewID and Code,
referencing the primary keys of PerformanceReviews and Departments
respectively. It would also have columns such as ReviewDate and
ReviewResult. The primary key of this third table would be a composite one
made up of the PeformanceReviewID, Code and ReviewDate columns as in
combination these will be unique.

I hope the above serves to illustrate the principles involved, but if you
can explain what entity type your main table represents we can probably be
more specific as to how your design needs amending.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

I don't see a problem there per se. The hypothetical example of
PerformanceReviews which I gave in my last reply to the OP would be a case in
point. A Quarterly Budget Compliance Review or Monthly Absenteeism Review
for instance would more than likely reference all departments in an
organisation.

Ken Sheridan
Stafford, England

 

[Klatuu]

I agree, but it is not that common. My post was meant only to raise the
question of whether the design is correct.

 

[John Bigness]

Thank you all for your help. This is not my db - I was asked to clean it up
but I now understand the issue here of the db not being normalized.