Results duplicating when Running Access Query

[bwlytkr]

Any thoughts on why I get some rows duplicating when I put any column header from the WLS07 table in the query. It is a very simple two table query and tied together with the option of include only those field where both fields are equal. I get no duplicates until I add data from the table, no matter what it is. I use this same query set up on multiple table and this is the only one I get duplicate rows on. I also have deleted this query and built another one and still the same issue. As you can see in the sql below there is only one entry from the WLS07 table, which is causing the issue. I don't know anything about writing or reading code so, I just don't know where to go from here. Any thoughts?

SELECT [WPC01 COMPL].MonthBeginDate, Format([MonthBeginDate],"mmmm") AS [Completed Month], WLS07.[Const Area Manager], [WPC01 COMPL].TicketNumber, [WPC01 COMPL].District, [WPC01 COMPL].SubDistrict, [WPC01 COMPL].WC_CLLI, [WPC01 COMPL].JobType, [WPC01 COMPL].createdByMember, [WPC01 COMPL].hasJob, [WPC01 COMPL].hasAfoDropsTicket, [WPC01 COMPL].isInPlan, [WPC01 COMPL].isInPlan_old, [WPC01 COMPL].NtgStart_MonthBeginDate, [WPC01 COMPL].completed_MonthBeginDate, [WPC01 COMPL].compType, [WPC01 COMPL].pxfrTicketId, [WPC01 COMPL].pxfrStepId, [WPC01 COMPL].TicketStatus, [WPC01 COMPL].StepStatus, [WPC01 COMPL].StepJobType, [WPC01 COMPL].Member, [WPC01 COMPL].JobIdentifier, [WPC01 COMPL].NextToGoStartDate, [WPC01 COMPL].CompletedDate, [WPC01 COMPL].reopenedDate, [WPC01 COMPL].State, [WPC01 COMPL].County, [WPC01 COMPL].Place, [WPC01 COMPL].StreetName, [WPC01 COMPL].HouseNumber, [WPC01 COMPL].CreatedByMember1, [WPC01 COMPL].CreatedBy, [WPC01 COMPL].createdOn, [WPC01 COMPL].updatedOn, [WPC01 COMPL].pulledFromNjuns_ts, [WPC01 COMPL].job_nm, [WPC01 COMPL].JobStatus, [WPC01 COMPL].fldNumStepsOp, [WPC01 COMPL].fldNumStepsCo, [WPC01 COMPL].fldLastWorkOnJob_dt, [WPC01 COMPL].Construction_Work_Comp_Dt, [WPC01 COMPL].JobAge_From_orig_approval_dt_Interval, [WPC01 COMPL].poleOwner, [WPC01 COMPL].StepRemarks, [WPC01 COMPL].FOK_Remarks, [WPC01 COMPL].FOK_CreatedBy, [WPC01 COMPL].poleOwnerName, IIf([isinplan]=True,"IN PLAN","NOT IN PLAN") AS [IN PLAN/NOT IN PLAN], IIf([compType]="JOB","1-EWO/PWO",IIf([compType]="FOK","2-FOK",IIf([compType]="AFO","3-AFO Drop",IIf([compType]="UNK","4-UNK")))) AS [COMPLETION TYPE], IIf([NextToGoStartDate]>-1,"NTG","Pending") AS [NJUNS TYPE OF COMPLETION NTG/PENDING]
FROM [WPC01 COMPL] INNER JOIN WLS07 ON [WPC01 COMPL].WC_CLLI = WLS07.clli
WHERE ((([WPC01 COMPL].District) Like "AL*"));

 

[Joe4]

When these sort of things happen, the first thing I look at is the join between the two tables:

INNER JOIN WLS07 ON [WPC01 COMPL].WC_CLLI = WLS07.clli

Are BOTH the "WC_LLI" field in the "WPC01 COMPL" table and "clli" field in the "WLS07" table unique fields?
Or are there any duplicates values in that field within one (or both) of the tables?
If it is not unique, then you probably do not have a 1-1 correspondence btween the two tables, which could result in duplicates in your query.

 

[bwlytkr]

When these sort of things happen, the first thing I look at is the join between the two tables:

INNER JOIN WLS07 ON [WPC01 COMPL].WC_CLLI = WLS07.clli

Are BOTH the "WC_LLI" field in the "WPC01 COMPL" table and "clli" field in the "WLS07" table unique fields?
Or are there any duplicates values in that field within one (or both) of the tables?
If it is not unique, then you probably do not have a 1-1 correspondence btween the two tables, which could result in duplicates in your query.

I found the issue. the WLS07 table had many CLLI Duplicates. I deleted the duplicates and it worked fine. Thanks for the help!

 

[Joe4]

Yep, that is what I figured.
You are welcome.