My query is repeating the results

[Carrie]

Can anyone tell me why my query is repeating the results like 2 or 3 times?

SELECT Event.AddUser, Call.Call_Num, Event.AddDate, Event.EventTime, Event.ExtraTime, Call.OpenDate, Call.AssignTo, Call.AssignDate, Call.ClosedBy, Call.ClosedDate, Call.Status, Call.Priority, Call.IsClosed, CallLog.ChangeType, CallLog.Description
FROM (((Call INNER JOIN Event ON Call.Call_ID = Event.Call_ID) INNER JOIN Org ON Call.Org_ID = Org.Org_ID) INNER JOIN (zSysDepartment INNER JOIN zSysUser ON zSysDepartment.Department_ID = zSysUser.Department_ID) ON Event.AddUser = zSysUser.UserName) INNER JOIN CallLog ON (Call.Call_ID = CallLog.Call_ID) AND (Org.Org_ID = CallLog.Org_ID)
WHERE (((Event.AddDate)>=[Start Date] And (Event.AddDate)<=[End Date]) AND ((zSysUser.EscalateTo)="CBRTKO") AND ((Org.Region_ID)="7"))
ORDER BY Event.AddUser, Call.Call_Num, Event.AddDate;

 

[Parra]

Hi Carrie,

This has happened in the past to me, usually when I don't have a primary key in the tables and the query is matching EVERY record to ALL the the records from the other table. This might be creating your duplicates.

Do you know about primary keys?

 

[Carrie]

I know what they are but how do I know if there is more than one key?

 

[Parra]

In the design view of the table, you would see a "little key" next to the field name.

Did I explain why duplicates were being produced?

 

[Carrie]

There are no keys in design view.

Yes, You said that I have more than one key and so it is doing the query for each key.

Okay, Here is the moment of truth. It was working fine. Then I did some thing in expression builder and properties and it came out giving me more than one answer. So I erased what I did but it is still duplicating.

 

[Parra]

No, what I said is that it usually happened when I did'nt have a primary key.

What the query is doing, is. Matching A record from your 1st table to ALL the records from your 2nd table. If your 1st table has 5 records and your second 10, then your query would produce 50 rows of data. We'll something like that.

How can you fix it, well. If it was working fine before you modified the expersion builder. Let me think about it and I'll get back to you.