Multi Table Complicated Relationships

[brharrii]

I'm trying to run a querry between 3 tables:

tlbProductInfo
- ProductID - PK
- ItemNumber
- JDEDescription
- FilmTopID - FK to tblPackaging
- RetailCaseID - FK to tblPackaging
- MasterCaseID - FK to tblPackaging

tblPackaging
- PackageID - PK
- JDEPackageID
- PackageDesc
- Packagetype - (used to filter which package materials are availabe in tblProductInfo under different package types ex. FilmTop, RetailCase, MasterCase)
- PackageType2 - FK to tblPackagingTypeData

tblPackagingTypeData
- PackageTypeID - PK
- PackageTypeDesc
- PackageTypeTare
- PackageTypeWeight
- PackageTypeUnits


Product X is sealed into 1 of 5 available "filmTopID".

Product X sealed in "film 1" is then packed into one of 20 or so different retail cases. We'll say it's packed into "retail case F".

Retail Cases:
Retail cases A, B, C and D are all 12oz bags,
Retail cases E, F, G, and H, are all 30oz Inners,
Retail cases I, J, and K are all 30 oz Bags,

This is where the tblPackagingTypeData comes into play.

All 30oz inners:
- have a tare weight of 0.068 lbs
- contain 12 units of product
- have a target end weight of 36 oz

12 units of Product X in "film 1" packed into "bag F" are then packed into "Master Case Y5"

Mastercases:
Master Case X1, X2, X3 are all 10 lb Boxes
Master Case Y4, Y5, Y6 are all 15 lb Boxes

All 15 lb Boxes:
- Have a Tare weight of 3 lbs
- contain 8 units of Retail cases
- Have a target weight of 15 lbs

My current querry looks like this:

SELECT tblProductInfo.ProductID, tblProductInfo.ItemNumber, tblProductInfo.JDEDescription, tblProductInfo.FilmTopID, tblProductInfo.RetailCaseID, tblProductInfo.MasterCaseID, tblPackaging.PackageType2, tlbPackageTypeData.PackageTypeTare, tlbPackageTypeData.PackageTypeWeight, tlbPackageTypeData.PackageTypeUnits
FROM tlbPackageTypeData LEFT JOIN (tblPackaging INNER JOIN tblProductInfo ON (tblPackaging.PackageID = tblProductInfo.MasterCaseID) AND (tblPackaging.PackageID = tblProductInfo.ComponentID) AND (tblPackaging.PackageID = tblProductInfo.DividerID) AND (tblPackaging.PackageID = tblProductInfo.RetailCaseID) AND (tblPackaging.PackageID = tblProductInfo.Liner2ID) AND (tblPackaging.PackageID = tblProductInfo.LIner1ID) AND (tblPackaging.PackageID = tblProductInfo.InsertID) AND (tblPackaging.PackageID = tblProductInfo.VPPocketID) AND (tblPackaging.PackageID = tblProductInfo.Label2ID) AND (tblPackaging.PackageID = tblProductInfo.Label1ID) AND (tblPackaging.PackageID = tblProductInfo.MasterCaseBottomID) AND (tblPackaging.PackageID = tblProductInfo.FilmBottomID) AND (tblPackaging.PackageID = tblProductInfo.FilmTopID)) ON tlbPackageTypeData.PackageTypeDesc = tblPackaging.PackageType2;

When I run it, I get an error that says, "Join Expression Not Supported". I've successfully run the query between tblPackaging and tblPackageTypeData, but when I add tblProductInfo it has problems.

I am not sure how to create a query when a single table has multiple fields that have relationships to another single table, I suspect that may be part of the issue but I'm not sure exactly where to start. Thank you for your assistance, sorry for the long question.

 

[AConrad00]

As an Access newbie, I've found it very helpful to create multiple queries that each perform a specific step instead of one master query that is hard to spot-check and even harder to maintain a working knowledge of what needs to be done to update it should something change

Name the queries with a number sequence so you know what order to run them, and then create a macro

S010_QueryName
s020_QueryName

This has been very helpful for someone like me who is just learning, and it might help you too