Duplicate records

[Zomboid]

Hey guys, I've done some searching and seen a lot of questions regarding to dealing with duplicate data entries. Unfortunately, none of these other threads have answered my particular version of duplicate troubles.

Here is the situation, I am trying to determine the effectiveness of employees by measuring the average of client responses that fall in a quarter. In this case the employees can serve the same client multiple times in a year, and thus have multiple entries of feed back.

So I have written a query that looks at: Year: Employee ID: Average Numerical Score Assigned to Customer Feedback.

The problem, which I think might stem from the fact that an employee can get 5 feedback scores on the same day from a client, and this multiple times a year, is that I end up duplicate employee IDs.

Here is an example from the resulting table:

Year EmployeeID AvgOfFeedback
20132014 4538 3
20132014 4538 3
20132014 4538 1.5
20132014 4219 2

What I would like to do is figure out how to combine the data behind that average for employees like 4538, then have the average feedback score calculated.

It is also worth noting here that there were some null and negative numbers in the feedback that I am choosing to ignore. Hopefully (but I am not sure) I've accomplished this by setting the query criteria for the feedback to >0.

 

[CJ_London]

basically, you can't average averages - you need to go back to the raw data

 

[obeylele]

The query you are describing should show single average for each EmployeeID. You must have another field grouping the results. are there any other fields in this query.

 

[Zomboid]

basically, you can't average averages - you need to go back to the raw data

Correct; However, I am not looking for an average of averages. This is mostly because when I am seeing duplicates I know that each "average" has a different N and so the average of the averages, while perhaps in the "ball park," will be skewed by not accounting for the weight of each entry.

The query you are describing should show single average for each EmployeeID. You must have another field grouping the results. are there any other fields in this query.

Yes, sort of. I've got 3 active terms in the query:

Field 1. YearName 2. Employee ID 3. NumericalScore
Table Pay Year Employee Data Feedback Data
Total Group by Group By Average
Sort Ascending
Criteria "20142015" ">0"

It is also worth noting that this query relies on 6 interconnected tables, only three of which I am asking information to be reported directly from. I am novice enough to access to not know whether or not the query is properly connecting and sorting data through all the tables it needs to.

Here are my tables:

Customer Information (connected to feedback and current client)
Customer Feedback (connected to customer information)
Current Client (connected to customer information and field reporting)
Field Reporting (connected to current client, pay year, employee information)
Pay Year (connected to field reporting)
Employee Information (connected to field reporting)

 

[obeylele]

Can you post screen shot of your query or SQL.

have you linked the tables in this query.
ie. [Employee ID] from Employee Data table should be linked to [Employee ID] in Feedback Data table.
also Table Pay Year should be liked to one of the other tables