Finding a related record that has 2 foreign keys

[verdes]

I am using access 2007

i have a materialsUsed tbl that list all the materials used in a job and the quantity of each.

(ID
Material Code
Quantity
Unit of Measure)

I also have 2 look-up tables - 1 for the material codes and 1 for the units of measure

I have another table, priceperunitofMeasure

id
material Code (FK to material lookup tbl)
Unit of Measure (FK to unit of measure tbl)
Price

In my form Users will select material code from combo box - enter the quantity, and select a unit of measure from a combo box.

For example: they could enter the following :

Paint 2 gallons
Paint 3 tubes
Paint 1 pint

How do I lookup the price per unit of measure for each of these records in the price per unit of meassure table? The price per unit of measure table has 2 foreign keys.

Help

 

[RainLover]

I believe you will need a Third Table.

PK (AutoNumber)
Product (The FK to the existing Table.)
UOM ( Again a FK to the other Table)
Price (Currency)

However if the prices are subject to change and you want to keep a history you will need yet another Table with the FK to the PK in the Table above plus a Price and an effective date.

Can get a little complicated.

EDIt

I hope I explained this properly. If not sing, out I will try again.

 

[Pat Hartman]

Welcome aboard
Your tables need some refinement. You are not using PK/FK correctly. An FK ALWAYS points to the PK of another table. Also, naming all primary keys "id" is poor practice. PKs should have a meaningful name that reflects the table they belong to. That gives you a meaningful name to use when you use them as FKs in different tables. You will occassionally have to deviate from this in the rare cases when you need two FKs in one table that point back to the lookup table. I made the assumption that you will copy the price and save it in the material used table. This is not a violation of normalization since price is a function of time and you don't want the price to change once it has been used for a material. As Rain said, you could also implement this with a change log on the price table but that is significantly harder to work with and unless you actually need a change log of prices, I wouldn't bother.
tblMaterial:
MaterialID (autonumber primary key)
MaterialCode
MaterialDesc
tblMaterialUM:
MaterialUMID (autonumber primary key)
MaterialID (FK to tblMaterial) (unique index fld1)
UOM (FK to tblUOM) (unique index fld2)
Price
tblMaterialUsed:
MaterialUsedID (autonumber primary key)
MaterialUMID (FK to tblMaterialUM)
Price (copied from tblMaterialUM)
Quantity

 

[RainLover]

Pat

That is an approach I have never used. It has the advantage with the price that once added to a field of its own can be changed for special circumstances. One does not need to stick with the listed price.

And you are right about my suggestion in that it does require some additionl coding which can be tricky.

 

[Pat Hartman]

Rain, I forgot to mention the changeable aspect of price when stored this way. So - if your app has a default price that is looked up in the product table but which can be overridden at runtime, storing it in the order detail record is the only viable option. I find that even in situations where I want to maintain price histories for analysis purpose, I still copy the price at the time an order was placed and save it with the order detail since it makes analyzing orders so much easier.