Upon further review/testing, I have found that it takes the number of records
in tblChangeItem and multiplies the records by that amount. For example, if
there are 2 records in tblChangeItem, it returns twice the records in
tblChangeItemDetail...if there 3 records in tblChangeItem, it returns triple
the records in tblChangeItemDetail.
Here is the SQL. Thanks for taking the time to check this out!
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 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 (Project.ProjectID = tblChangeRequest.ProjectID) AND
(tblChangeItem.ProjectID = tblChangeRequest.ProjectID) AND (tblChangeItem.
ChangeRequestID = tblChangeRequest.ChangeRequestID)) ON Labor.CBDCode =
Product.CBDCode
ORDER BY Project.ProjectID, tblChangeItem.RoomNumber & " - " & tblChangeItem.
ItemNumber;
Jason said:
What could cause a query to return a duplicate set of records? I can view
the records in the table, and for example there may be 6 records associated
[quoted text clipped - 27 lines]
Post the SQL of your query and we'll give it a look-see