Access 2007- Query Repeating Records 1

[glynnmack]

Hi all,

I'm building an access database to track documents required and received at different stages of a project. The query I'm designing is intended to create a record of the Equipment Type, Equipment Tag, and Document Type that was submitted with bid. Should be a pretty simple query, but although I only have 4 Documents, I'm getting 412 records. Am I missing something where the query is getting information from some related tables that I haven't listed in the query specifically? Is my query out of order?

SQL
SELECT tblProjectEquipment.EquipmentType, tblProjectEquipment.EquipmentTag, tblDocumentRec.DocumentType
FROM tblReqDoc, (tblProjectEquipment INNER JOIN tblDocumentRec ON tblProjectEquipment.EquipmentTag = tblDocumentRec.EquipmentTag) INNER JOIN tblSubmittalPackageRec ON (tblSubmittalPackageRec.Vendor = tblDocumentRec.Vendor) AND (tblSubmittalPackageRec.EquipmentTag = tblDocumentRec.EquipmentTag) AND (tblSubmittalPackageRec.SubmittalPackageID = tblDocumentRec.SubmittalPackageID) AND (tblProjectEquipment.EquipmentTag = tblSubmittalPackageRec.EquipmentTag)
WHERE ((([tblSubmittalPackageRec]![Bid])=-1));

Thanks!

 

[BillPSU]

Strip the query down to sections and see the results as you add sections. Your criteria is causing multiple records from at least one of the inner joins and it may be serveral inner joins are causing multiple records. You will have to look carefully at your data and your results.

 

[glynnmack]

I found that I was creating a Cartesian product. Basically, the joins and order of my query (I was looking for EquipmentType in tblReqDoc that matched those in tblProjectEquipment, instead of vice versa) caused 4 records for each of the 103 matching records in tblReqDoc. Or so I think...

Anyway, thanks for your assistance!