S
Slez via AccessMonster.com
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;
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;