Multiple Fields from 1 table related to a single ref. table?

[Anonymous]

Hi everyone,

Here's one I've been wrestling with. Very simple Access DB laid out as
follows:

Table1 is called "tItems"
IndexID - Autonumber (Primary Key)
Item1 - Text
Item1UOM - Number (Links to Unit of Measure reference table)
Item2 - Text
Item2UOM - Number (Links to Unit of Measure reference table)

Table2 is called "tRefUOM"
UOMID - Number (Primary Key) - Links back to Item1UOM as a 1 to Many and
also links back to Item2UOM as a 1 to Many
UOMDesc - Text

I'm getting hung up on reference tables and relationships. Ultimately, I
only want to have a single reference table for units of measure (as shown
below):

1 Pounds
2 KG
3 Grams

That way, if I have a particualr row that has a value of 1 for Item1UOM,
then I should be able to write a query that pipes in the correct desc
(Pounds) and if that same record has a Ivalue of 3 for Item2UOM, then my
query should return the correct desc (Grams) for that item. Access is
getting hung up on piping in the desc for two different fields from a single
reference table. When I defined my relationships in access, I thought I
could have both UOM fields link to the single reference table (tRefUOM).
However, I'm finding that even though the relationsip window let's me do
this, this approach does not work when I try to make queries that pipe in
the assocated desc value for the UOM in the tItems table. For example,
here's the query that does not work:

SELECT tItems.IndexID, tItems.Item1, tItems.Item1UOM, tRefUOM.UOMDesc,
tItems.Item2, tItems.Item2UOM, tRefUOM.UOMDesc
FROM tRefUOM RIGHT JOIN tItems ON (tRefUOM.UOMID = tItems.Item2UOM) AND
(tRefUOM.UOMID = tItems.Item1UOM);

Question
1. Does anyone out there now if you can make a single reference table link
to multiple fields in another table as I'm trying to do above? If so, how
do you set it up correctly in the relationships window? I've gotten around
this by making a dedicated reference table with equivalent values for each
unique field that I'm trying to link to in the main table. This seems crazy
because if I ever get 4 or 10 items on the tItems table, I'd need 4 or 10
unique reference tables. I can't imagine this is the right way to do it.
Maybe my table design is bad?

Many Thanks!

 

[Allen Browne]

You have repeating values in your fields (Item1, Item2, ...). This always
indicates that you need a related table where these become multiple
*records* instead of multiple fields in the one table.

For an example, open the Northwind sample database, and choose Relationships
on the Tools menu. One order can contain multiple items, but instead of the
Orders table having fields for Item1, Item2, ..., you will see a related
table called Order Details. One order can now have as many items as needed
(rows in the related table.)

Once you have done that, it will be easy to sort out the unit of
measurement.

 

[MX1]

Interesting approach. I'll give it a shot. Thanks!