Every record is coming out twice on the query

K

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#));
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top