Query produces duplicate records (1 Viewer)

[Spentak]

I cannot figure out how to stop my query from producting duplicate records. it only happens on the field "TestName"

Ive tried all kinds of things including using DISTINCT and DISTINCT ROW

Here is some sql that duplicates:
SELECT tblAccountInfo.Agency, tblAccountInfo.ReferringPerson, tblClientAccounts.ClientAccountID, tblClientAccounts.AccountID, tblClientAccounts.CID, tblClientAccounts.Cutoff, tblDateTime.DonorResult, tblDateTime.DateofCollection, tblDateTime.DateofTest, tblDateTime.TimeofAppearance, tblDateTime.Temperature, tblDateTime.Creatinine, tblDateTime.SpecificGravity, tblDateTime.Notes, tblDonorInfo.LastName, tblDonorInfo.FirstName, tblDonorInfo.DateofBirth, tblDonorInfo.ID, tblReferringPerson.LastName1, tblReferringPerson.FirstName1, tblResults.TestID, tblResults.Results, tblResults.ResultsValue, tblTestAvail.TestName, [LastName] & " , " & [Firstname] & " - " & [DateofBirth] & " -- " & [ID] AS Name, [LastName1] & " , " & [firstname1] AS Ref, tblAccountInfo.Address, [City] & " , " & [State] & " - " & [Zipcode] AS [Add], tblDateTime.DateTimeID
FROM tblDonorInfo INNER JOIN ((((tblAccountInfo INNER JOIN tblClientAccounts ON tblAccountInfo.AccountID = tblClientAccounts.AccountID) INNER JOIN tblReferringPerson ON tblAccountInfo.AccountID = tblReferringPerson.AccountID) INNER JOIN tblDateTime ON tblClientAccounts.ClientAccountID = tblDateTime.ClientAccountID) INNER JOIN (tblTestAvail INNER JOIN tblResults ON tblTestAvail.TestID = tblResults.TestID) ON tblDateTime.DateTimeID = tblResults.DateTimeID) ON tblDonorInfo.CID = tblClientAccounts.CID
GROUP BY tblAccountInfo.Agency, tblAccountInfo.ReferringPerson, tblClientAccounts.ClientAccountID, tblClientAccounts.AccountID, tblClientAccounts.CID, tblClientAccounts.Cutoff, tblDateTime.DonorResult, tblDateTime.DateofCollection, tblDateTime.DateofTest, tblDateTime.TimeofAppearance, tblDateTime.Temperature, tblDateTime.Creatinine, tblDateTime.SpecificGravity, tblDateTime.Notes, tblDonorInfo.LastName, tblDonorInfo.FirstName, tblDonorInfo.DateofBirth, tblDonorInfo.ID, tblReferringPerson.LastName1, tblReferringPerson.FirstName1, tblResults.TestID, tblResults.Results, tblResults.ResultsValue, tblTestAvail.TestName, [LastName] & " , " & [Firstname] & " - " & [DateofBirth] & " -- " & [ID], [LastName1] & " , " & [firstname1], tblAccountInfo.Address, [City] & " , " & [State] & " - " & [Zipcode], tblDateTime.DateTimeID
HAVING (((tblDateTime.DateTimeID)=[Forms]![frmEnterResults]![frmEnterResultsSub].[Form]![DateTimeID]));


here is another:SELECT DISTINCTROW tblAccountInfo.Agency, tblClientAccounts.ReferringPerson, [City] & " , " & [State] & " - " & [Zipcode] AS [Add], [LastName] & " , " & [Firstname] & " - " & [DateofBirth] & " -- " & [ID] AS Name, [LastName1] & " , " & [firstname1] AS Ref, tblClientAccounts.Cutoff, tblAccountInfo.Address, tblDateTime.DonorResult, tblDateTime.DateofCollection, tblDateTime.DateofTest, tblDateTime.TimeofAppearance, tblDateTime.Temperature, tblDateTime.Creatinine, tblDateTime.SpecificGravity, tblDateTime.Notes, tblResults.Results, tblResults.ResultsValue, tblTestAvail.TestName, tblDonorInfo.CID, tblAccountInfo.AccountID, tblReferringPerson.RefPerID, tblDateTime.DateTimeID, tblResults.ResultsID, tblTestAvail.TestID, tblClientAccounts.ClientAccountID, *
FROM tblDonorInfo INNER JOIN ((((tblAccountInfo INNER JOIN tblClientAccounts ON tblAccountInfo.AccountID = tblClientAccounts.AccountID) INNER JOIN tblReferringPerson ON tblAccountInfo.AccountID = tblReferringPerson.AccountID) INNER JOIN tblDateTime ON tblClientAccounts.ClientAccountID = tblDateTime.ClientAccountID) INNER JOIN (tblTestAvail INNER JOIN tblResults ON tblTestAvail.TestID = tblResults.TestID) ON tblDateTime.DateTimeID = tblResults.DateTimeID) ON tblDonorInfo.CID = tblClientAccounts.CID;

 

[Guus2005]

The results you get are not double. There is at least one field different from the rest.

 

[Spentak]

?

What do you mean? Do you have an idea of how i can fix this?

 

[Guus2005]

1- You have to pinpoint the field which gives you different values, thus causing the query to give you apparently double records (which they are not).

2- in your second query, remove the ,* part, might work. Otherwise, see 1