Every record is coming out twice on the query

[KevinS]

Below is the code for a query I am running. For some reason every records is
coming out twice.

I have even gone so far as to eliminate one field at a time and still get
the same results. Can you tell me why it is repeating and what I can about
it to prevent it from happening again.
Thank you,
Kevin

SELECT IntakeMain.ReferDate, IntakeMain.KLName, IntakeMain.KFName,
IntakeMain.[In Home] AS Expr1, [Individual Clearances].ID, [Individual
Clearances].DueDate, [Individual Clearances].SentDate, [Individual
Clearances].RecDate, [Individual Clearances].Comments
FROM (IntakeKinship INNER JOIN ((IntakeMain LEFT JOIN [frmsrc Caregivers and
Kin] ON IntakeMain.KSSN = [frmsrc Caregivers and Kin].KSSN) INNER JOIN
IntakeChild ON IntakeMain.CaseID = IntakeChild.CaseID) ON IntakeKinship.KSSN
= IntakeMain.KSSN) INNER JOIN [Individual Clearances] ON IntakeMain.KSSN =
[Individual Clearances].KSSN
WHERE ((([Individual Clearances].DueDate)>#1/1/2006#));

 

[John Spencer MVP]

Simplest solution if the records are exact duplicates is to use DISTINCT operator

SELECT DISTINCT IntakeMain.ReferDate
, IntakeMain.KLName
, IntakeMain.KFName
, IntakeMain.[In Home] AS Expr1
, [Individual Clearances].ID
, [Individual Clearances].DueDate
, [Individual Clearances].SentDate
, [Individual Clearances].RecDate
, [Individual Clearances].Comments
FROM (IntakeKinship INNER JOIN
((IntakeMain LEFT JOIN
[frmsrc Caregivers and Kin]
ON IntakeMain.KSSN = [frmsrc Caregivers and Kin].KSSN) INNER JOIN
IntakeChild ON IntakeMain.CaseID = IntakeChild.CaseID)
ON IntakeKinship.KSSN = IntakeMain.KSSN) INNER JOIN
[Individual Clearances]
ON IntakeMain.KSSN = [Individual Clearances].KSSN
WHERE ((([Individual Clearances].DueDate)>#1/1/2006#));

If you are getting duplicates, then the problem lies in the tables being
joined. Someplace in all those joins you are getting duplicates. Do you need
all the tables you are using in the FROM clause to get the results you want?

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County