trouble with query results

I have a query that returns duplicate records as follows:

If I have 1 record in tblChangeItem, it returns the records for
tblChangeItemDetail properly.
If I have 2 records in tblChangeItem, it returns double the records for
tblChangeItemDetail.
If I have 3 records in tbleChangeItem, it returns the records for
tblChangeItemDetail in triplicate.
...and so on...

Right now, Unique Values and Unique Records is set to No. I have tried
seting them to Yes, but it did not change the results of the query. I
suspect that something with my JOINS in the query is not quite right, but
I've not been successful at identifying the problem. Any suggestions on
where to start? Thanks in advance for any help!

Here is my query's SQL:
SELECT Project.ProjectID, Project.ProjectName, Project.JobNumber,
tblChangeRequest.ChangeRequestID, tblChangeRequest.CRDate, tblChangeRequest.
Estimator, Project.SalesTax, Project.TaxExempt, tblChangeRequest.
SalesTaxAmount, tblChangeRequest.UseTaxAmount, tblChangeItem.RoomNumber & " -
" & tblChangeItem.ItemNumber AS ItemLabel, [Quantity]*([UnitPrice]+[QuoteCost]
) AS LineTotalCost, [LineTotalCost]*[Markup] AS SellPrice, [Quantity]*(
[QuoteCost]+[MaterialCost]) AS LineMaterialCost, [Quantity]*(
[MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor,
tblChangeItemDetail.Quantity, tblChangeItemDetail.ProductDescription,
tblChangeItemDetail.Markup, tblChangeItemDetail.QuoteCost, Product.
ProductCode, Product.CBDCode, Product.MachineLaborHours, Product.
BuildingLaborHours, Product.MaterialCost, Product.UOM, Product.UnitCost,
Labor.CBDCodeID, Product.LibraryReference, tblChangeRequest.ChangeDescription,
tblChangeItem.ItemDescription

FROM ((Labor INNER JOIN Product ON Labor.CBDCode = Product.CBDCode) INNER
JOIN ((Project INNER JOIN tblChangeItem ON Project.ProjectID = tblChangeItem.
ProjectID) INNER JOIN tblChangeItemDetail ON (tblChangeItem.ChangeRequestID =
tblChangeItemDetail.ChangeRequestID) AND (tblChangeItem.ProjectID =
tblChangeItemDetail.ProjectID) AND (Project.ProjectID = tblChangeItemDetail.
ProjectID)) ON Product.ProductDescription = tblChangeItemDetail.
ProductDescription) INNER JOIN tblChangeRequest ON (tblChangeRequest.
ChangeRequestID = tblChangeItem.ChangeRequestID) AND (tblChangeRequest.
ProjectID = tblChangeItem.ProjectID) AND (Project.ProjectID =
tblChangeRequest.ProjectID)

ORDER BY Project.ProjectID, tblChangeItem.RoomNumber & " - " & tblChangeItem.
ItemNumber;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

What are the primary keys fields for the following tables?

Labor
Product
Project
tblChangeItem
tblChangeItemDetail
tblChangeRequest

My bet is that you don't have a primary key or at least unique indexed
field(s) on one side of the join which you should have to ensure a
one-to-many relationship. In that case you could have duplicate matching
values on both sides of the join, AKA a many-to-many relationship, which can
cause the behavior that you are describing.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Slez via AccessMonster.com" wrote:

I have a query that returns duplicate records as follows:

If I have 1 record in tblChangeItem, it returns the records for
tblChangeItemDetail properly.
If I have 2 records in tblChangeItem, it returns double the records for
tblChangeItemDetail.
If I have 3 records in tbleChangeItem, it returns the records for
tblChangeItemDetail in triplicate.
...and so on...

Right now, Unique Values and Unique Records is set to No. I have tried
seting them to Yes, but it did not change the results of the query. I
suspect that something with my JOINS in the query is not quite right, but
I've not been successful at identifying the problem. Any suggestions on
where to start? Thanks in advance for any help!

Here is my query's SQL:
SELECT Project.ProjectID, Project.ProjectName, Project.JobNumber,
tblChangeRequest.ChangeRequestID, tblChangeRequest.CRDate, tblChangeRequest.
Estimator, Project.SalesTax, Project.TaxExempt, tblChangeRequest.
SalesTaxAmount, tblChangeRequest.UseTaxAmount, tblChangeItem.RoomNumber & " -
" & tblChangeItem.ItemNumber AS ItemLabel, [Quantity]*([UnitPrice]+[QuoteCost]
) AS LineTotalCost, [LineTotalCost]*[Markup] AS SellPrice, [Quantity]*(
[QuoteCost]+[MaterialCost]) AS LineMaterialCost, [Quantity]*(
[MachineLaborHours]+[BuildingLaborHours]) AS LineTotalLabor,
tblChangeItemDetail.Quantity, tblChangeItemDetail.ProductDescription,
tblChangeItemDetail.Markup, tblChangeItemDetail.QuoteCost, Product.
ProductCode, Product.CBDCode, Product.MachineLaborHours, Product.
BuildingLaborHours, Product.MaterialCost, Product.UOM, Product.UnitCost,
Labor.CBDCodeID, Product.LibraryReference, tblChangeRequest.ChangeDescription,
tblChangeItem.ItemDescription

FROM ((Labor INNER JOIN Product ON Labor.CBDCode = Product.CBDCode) INNER
JOIN ((Project INNER JOIN tblChangeItem ON Project.ProjectID = tblChangeItem.
ProjectID) INNER JOIN tblChangeItemDetail ON (tblChangeItem.ChangeRequestID =
tblChangeItemDetail.ChangeRequestID) AND (tblChangeItem.ProjectID =
tblChangeItemDetail.ProjectID) AND (Project.ProjectID = tblChangeItemDetail.
ProjectID)) ON Product.ProductDescription = tblChangeItemDetail.
ProductDescription) INNER JOIN tblChangeRequest ON (tblChangeRequest.
ChangeRequestID = tblChangeItem.ChangeRequestID) AND (tblChangeRequest.
ProjectID = tblChangeItem.ProjectID) AND (Project.ProjectID =
tblChangeRequest.ProjectID)

ORDER BY Project.ProjectID, tblChangeItem.RoomNumber & " - " & tblChangeItem.
ItemNumber;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1