Query is producing triplicates

[TraciAnnNeedsHelp]

Here is the SQL:
SELECT dbo_vCTRequest.RequestID, dbo_vCTRequestPartDetail.RequestPartID,
dbo_vCTRequestPartDetail.PartNum, dbo_vCTRequestPartDetail.Quantity,
dbo_vCTRequestPartDetail.Cost, dbo_vCTRequestPartDetail.PartStatus,
dbo_vCTRequestPartDetail.ReturnedGood,
dbo_vCTRequestPartDetail.InboundTracking,
dbo_vCTRequestPartDetail.DefectiveReturnTracking, IIf([ReturnedGood] In
(1),-[Cost],0) AS CreditAmount, Nz([Cost])+Nz([CreditAmount]) AS Total
FROM (dbo_vCTRequest INNER JOIN dbo_vCTRequestTime ON
dbo_vCTRequest.RequestID = dbo_vCTRequestTime.RequestID) INNER JOIN
dbo_vCTRequestPartDetail ON dbo_vCTRequest.RequestID =
dbo_vCTRequestPartDetail.RequestID
WHERE (((dbo_vCTRequest.ExpectedArrivedOnsite) Between [Enter Start Date]
And [Enter End Date]) AND ((dbo_vCTRequest.ProjectID)=85))
ORDER BY dbo_vCTRequest.RequestID, dbo_vCTRequestPartDetail.RequestPartID;

Thanks!
TraciAnn

 

[Jerry Whittle]

Probably something to do with the joins between the three tables. Hopefully
dbo_vCTRequest.RequestID is the primary key for that table. Otherwise that
could be the problem right there with duplicate data.

A fast fix may be to change the SQL statement from SELECT to SELECT
DISTINCT. That should get rid of exact duplicates at the cost of some
peformance.

 

[TraciAnnNeedsHelp]

I was originally going to use a field from the dbo_vCTRequestTime table but
didn't need it, but I left the table in the view.

By removing the table from the view. The duplicates went away. I don't
understand why this would have affected it, but it worked.

What would have happened if I needed a field from that table?

Probably something to do with the joins between the three tables. Hopefully
dbo_vCTRequest.RequestID is the primary key for that table. Otherwise that
could be the problem right there with duplicate data.

A fast fix may be to change the SQL statement from SELECT to SELECT
DISTINCT. That should get rid of exact duplicates at the cost of some
peformance.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL:
SELECT dbo_vCTRequest.RequestID, dbo_vCTRequestPartDetail.RequestPartID,
dbo_vCTRequestPartDetail.PartNum, dbo_vCTRequestPartDetail.Quantity,
dbo_vCTRequestPartDetail.Cost, dbo_vCTRequestPartDetail.PartStatus,
dbo_vCTRequestPartDetail.ReturnedGood,
dbo_vCTRequestPartDetail.InboundTracking,
dbo_vCTRequestPartDetail.DefectiveReturnTracking, IIf([ReturnedGood] In
(1),-[Cost],0) AS CreditAmount, Nz([Cost])+Nz([CreditAmount]) AS Total
FROM (dbo_vCTRequest INNER JOIN dbo_vCTRequestTime ON
dbo_vCTRequest.RequestID = dbo_vCTRequestTime.RequestID) INNER JOIN
dbo_vCTRequestPartDetail ON dbo_vCTRequest.RequestID =
dbo_vCTRequestPartDetail.RequestID
WHERE (((dbo_vCTRequest.ExpectedArrivedOnsite) Between [Enter Start Date]
And [Enter End Date]) AND ((dbo_vCTRequest.ProjectID)=85))
ORDER BY dbo_vCTRequest.RequestID, dbo_vCTRequestPartDetail.RequestPartID;

Thanks!
TraciAnn

 

[John W. Vinson]

I was originally going to use a field from the dbo_vCTRequestTime table but
didn't need it, but I left the table in the view.

By removing the table from the view. The duplicates went away. I don't
understand why this would have affected it, but it worked.

Evidently there were three records in dbo_vCTRequestTime which matched that
particular RequestID. If there had been ten matching records you'ld have had
dectuplicates.

What would have happened if I needed a field from that table?

Which *record* in that table? What if there were ten matching records, with
ten values of the field that you need? You'ld want to see all ten, right?