Query pulling the same data three times.

[halabis]

I have a query designed to pull an agent's attendance data. Unfortunately, when I run it the data is always being pulled in triplicate. What is causing this, and how do I stop it?



Thanks!

SQL (Which I don't really understand at all.)
SELECT Agent.[Agent Name], Agent.[Team Manager], [Corrective Action Level].[Update Date], [Corrective Action Level].[Corrective Action Level], Occurrences.[Occurrence Date], Occurrences.[Minutes Missed], Occurrences.[Counted Against], IIf([Counted Against]="Yes",IIf([Minutes Missed]>=240,1,IIf([Minutes Missed]>=180,0.8,IIf([Minutes Missed]>=120,0.6,IIf([Minutes Missed]>=60,0.4,IIf([Minutes Missed]>=5,0.2,0))))),0) AS Occurrences
FROM (Agent INNER JOIN Occurrences ON Agent.[Agent Name] = Occurrences.[Agent Name]) INNER JOIN [Corrective Action Level] ON Agent.[Agent Name] = [Corrective Action Level].[Agent Name]
WHERE (((Agent.[Agent Name])=[Forms]![Attendance Forms].[NavigationSubform].[form]![PullAgentReport]));

[JoeM]

It means that you have a one-to-many (or many-to-many) relationship between your tables.
If you look at one of your records that is being pulled in triplicate, look at your Occurences table and your Corrective Action Level table and check and see how many time that Agent Name appears in each of those tables. At least one of them probably has three records for that Agent Name.

[NTC]

as JoeM points out - this is just the way it is in a 1-Many relationship. Queries never are in error. They may not be what you expect or what you want - but they are never incorrect. So what you want to do, assuming your tables & joins are the way the should be - is to define this as a Distinct query; to do that - right click in the upper section of the query design view and go to properties (some times one must first left click in the upper section before right click to see the properties choice)..... in properties a few rows down from the top is Unique Values property. The default is no and change that to yes.

[JoeM]

So what you want to do, assuming your tables & joins are the way the should be - is to define this as a Distinct query; to do that - right click in the upper section of the query design view and go to properties (some times one must first left click in the upper section before right click to see the properties choice)..... in properties a few rows down from the top is Unique Values property. The default is no and change that to yes.

Just to follow-up on this, be aware that this only works if ALL the fields you are returning are exactly the same value. I have seen people get tripped up by that fact (one of the fields that they are returning in the query is not duplicated). Also, another way to do this is to switch your query to SQL View (which you did to copy and paste it here), and put the word DISTINCT after the first SELECT.

Another way of doing this is to create an Aggregate (Totals) Query. You would use "Group By" all the duplicate fields.
Aggregate Queries are also useful if you wanted to "Group By" most fields, but want to take some mathematical function of another field (like Count, Sum, Min, Max, First, Last, etc).

[June7]

What do you intend to do with this query? Maybe instead you should build a report/subreport(s) arrangement.

Why is CorrectiveAction linked to agent? Should it be linked to Occurrences? Corrective action for what - an occurrence?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[halabis]

The Agent table stores each employee once as a unique record, and has data that is only ever stored once.

The Occurrence table tracks each attendance infraction, including the date, and reason for the infraction.

The Corrective Action Level table tracks each time an employees corrective action level changes, which is independent of the other factors.

My goal for the report that draws from this query is to be able to display for a selected agent their name, then a list of each time they are absent and why, as well as their current corrective action level. Currently the report does all of this, except that it pulls each record multiple time. As someone mentioned earlier I believe it is pulling in triplicate because my test data has 3 entries on the Occurrences table for the agent I am using as the test subject.

[June7]

That is correct.

Does Corrective Action have only one record for each agent?

Perhaps you just need to do Sorting & Grouping on the report.

Create a group on the Agent Name.

BTW, names make very poor unique identifiers. Should have an AgentID field and then name parts should be in separate fields: LastName, FirstName, MidInit, Title, Suffix

[halabis]

Corrective action has multiple records for each agent, we need to be able to track their corrective action history (although that will be a separate report)

The names ([Lastname, Firstname] format) unfortunately have to be in one field to match our other legacy reporting systems that I have no control over. If I had my preference they would definitely be separated out.

[June7]

But why are you not using AgentID field as PK/FK for your tables? Even if you store name parts in separate fields, they can be concatenated with expressions in query. It is easier to concatenate than split up text.

Since you have two tables with multiple records for each agent, you probably need to do a report/subreport arrangement. Alternatively, if you just want summary info (like count or sum) and don't care about details of the multiple records, build aggregate queries first and join those queries to Agent table.

[halabis]

Because I'm bad at database design!

I offered to make a simple db for my boss once, and now I'm "the access guy." I keep getting tapped to make complex DB's way above my skill level. On the up side, my skill level keeps going up. I'm not sure what the pros/cons of making different values the primary key are.

Do you have a link literature on how to do a report/subreport arrangement?

Thanks!

[June7]

Build the same way as a form/subform.