Relationship Issues?

[RatBronco]

II have a DB I am working on and have stumbled onto a
problem.
Basically, I have a lighting fixture that
can hold up to three lamps in any combination including no lamp in a
socket.
Using Access
2007


Orders
Orders_ID
PO#
Qty
FixtureID (PK)

tblFixtures
Fix_ID
FixtureID (PK)
Socket1
Socket2
Socket3

tblLamps
Lamp_ID


LampCode (PK)
LampCost
Description


Report
(How many lamps of each kind do I need to buy and the cost of each
lamp and total of all lamps?)

Qty – Each type of lamp – Lamp description - Lamp Cost – Lamp Total (including all lamp types from any sockets)

22 L001 25w $2.15 $47.30
15 L002 40w $2.25 $33.75
42 L003 60w $2.50 $105.00

Query

Qty-FixtureID-Socket1-Description-LampCost-Socket2-Description-LampCost-Socket3-Description-LampCost

I am only getting the first socket data.
Data for the 2nd an 3rd cost and description is duplicated
from the first socket

Relationship

One - tblFixtures.FixtureID to Many tblOrders.FixtureID
One - tblFixtures.Socket1, Socket2, Socket3 to Many tblLamps.LampCode

Join Properties is all records from Include ALL records from 'tblFixtures' and
only those records from
'tblLamps' where the joined fields are equal.

There are three links from Socket1, Socket2 and Socket3 to
tblLamps.LampCode

I tried the three join
options with no luck.

I think the problem lies
with the relationship between tblLamps and tblFixture but I just can't get it.
I just want to be able to pull the cost and description for each socket on a report, based off of the LampCode there will be three lamps per fixture.
Hope I included enough info.

[pbaldy]

For starters, I think the normalized design would have a related table for the sockets, not repeating fields. To keep it like it is, you don't join all 3 fields to the other table. You would add the table to the query 3 times, and join each field to a different instance of the table. Access will alias the second 2.

[RatBronco]

Thanks for the quick response, wow!
Ahhhh…Aliases. Worked like a charm, but a few questions:
The example that I gave was just to demonstrate the problem I was having. The DB I’m working on has a table called “Conversions” with all of the items needed for a conversion. There could be up to 15 items for each conversion that may have two different items. All second items will need aliases.
Lamp1
Lamp2
Ballast1
Ballast2
Conkit1
ConKit2
Fixture1
Fixture2… and so on for 11 more items

And now to address the limitations of Access. Although Access is proving to be a powerful solution to that cumbersome spreadsheet, everything has limitations. How many times will I be able to create one alias for each item? I’m looking at 15 instances of aliases now.

[pbaldy]

Well, this goes back to the normalization issue I mentioned. You've created the limitation with your design. A similar example would be an invoice. The normalized design is an invoice header table with invoice number, customer, date, etc. Then you have a details table with fields for invoice number, item, quantity, cost, etc. Those tables would be related one-to-many on the invoice number, and the details table would have a record for each product purchased. That lets you have 1 item or 1,000 on an invoice; no limitations. Here's one of many links on the subject:

http://www3.ntu.edu.sg/home/ehchua/p...se_Design.html