Problem with joins in a cartesian query

[bigalpha]

I am using a cartesian query to create multiple copies of a record to use for printing labels.

Here's my query that produces the cartesian result:

SELECT tblCount.CountID, tblDeliveryOrders.DeliveryOrderNum, CurrentCY.Deliveryorderlineitemnum, CurrentCY.Quant, CurrentCY.UOM1, tblContainerSizeCodes.SizeCode, tblContainerTypeCodes.TypeCode, CurrentCY.WasteDescription, tblEtidDodaac.EtidDodaac, CurrentCY.ETIDDocNum, CurrentCY.Pounds, tblEPAWasteCodes.[EPAWasteCodes(1B)]
FROM tblCount, tblDeliveryOrders INNER JOIN (tblEtidDodaac INNER JOIN (tblContainerSizeCodes INNER JOIN (tblContainerTypeCodes INNER JOIN (tblEPAWasteCodes INNER JOIN CurrentCY ON tblEPAWasteCodes.EpaWasteCodesIDPK = CurrentCY.[EPAWasteCodes(1B)IDFK]) ON tblContainerTypeCodes.ContainerTypeCodesIDPK = CurrentCY.ContainerTypeCodeIDFK) ON tblContainerSizeCodes.ContainerSizeCodesIDPK = CurrentCY.ContainerSizeCodeIDFK) ON tblEtidDodaac.EtidDodaacIDPK = CurrentCY.EtidDodaacIDFK) ON tblDeliveryOrders.DoIDPK = CurrentCY.DeliveryOrderNumberIDFK
WHERE (((tblCount.CountID)<=[quant]) AND ((tblDeliveryOrders.DeliveryOrderNum)=[which delivery order]));

This works just fine in creating the desired result - EXCEPT I don't get all the records.

When I remove the 'cartesian table', and right join everything, then I get the correct results. If I keep everything as-is and reintroduce the cartesian table, then I get an error about there being an ambiguous outer join.

I searched but I couldn't figure this out. TIA

 

[plog]

An INNER JOIN only shows records that match between the two sides of the joins. A RIGHT JOIN shows all the records from the ride side of the join, no matter what's in the left side. So that means one of your INNER JOINS doesn't have all the values it is joined on.

To find out the culprit, create a new query for every join and bring in both datasources of that join. INNER JOIN them as you've shown above, bring down the joined fields from both datasources, run it and note the record count. Then change the join to a RIGHT JOIN in the manner you said worked and compare the record count to the INNER JOIN count--if there's a difference that join is at least part of the problem. To find the specific values, sort the right joined query ascending by the joined field on the left side of the right join (the non-mandatory field). Any nulls are the ones not in the left side and causing you to get fewer results when you INNER JOIN them.

 

[bigalpha]

Ok, so I found the join that, if left as INNER JOIN, will limit the number of records the query will produce (I previously made all the tables INNER JOINs). Is there a problem with making all the joins INNER?

When I initially researched my problem I figured it was a problem with the way the system was prioritizing the joins and I couldn't, for the life of me, figure out how to create a subquery. It just now struck me that I can add a query as a table when creating another query, and reference those fields directly.

*facepalm* So the solution to this was actually easy, but I'm a little dense!