Misbehaving Report

[CanadianAccessUser]

Hello!

I have a report that shows Agent stats which works great for nearly 100 agents. One agent in particular however, is showing stats that are double what they should be. I've looked in the tables and queries and see no evidence of duplicated data. Is there a reason why one agent would have their stats multiplied exactly by 2 when the rest aren't?



Thanks,
Canadian

 

[Simon_MT]

Exclude all records apart from the miscreant Agent record and look at the underlying records.

Simon

 

[CanadianAccessUser]

Please excuse my confusion but what do you mean by underlying records?

 

[Simon_MT]

Select all records for that one agent. If a information is summarized then take out the totalling and go down to individual transactions. You need to do a forensic examination of the data integrity.

Simon

 

[CanadianAccessUser]

I've done what you asked and found nothing sketchy in my data (checked tables and all related queries). I did however find that the exact same thing is happening when I run a January report only it's a totally different agent...
I have another report that runs similar information and both agents in question have the correct totals in that report.

 

[CanadianAccessUser]

UPDATE:
When I run each day in the month and add them up myself I get the right number.
When I run the full month it gives me exactly double the right number...

 

[jdraw]

What is the recordsource of the report?
Can you show us a jpg of your tables and relationships? And/or a copy of your database --remove anything confidential - then zip it.

 

[CanadianAccessUser]

Here are my relationships.
Since there are past employees I have connected all tables/queries through the active agents query limiting all data and drop downs for agents to the current agents.

 

[jdraw]

Why do you have tables names "q..."?
What exactly are the business rules/business description of what this database is supposed to support?

In 3 or 4 lines WHAT is this database about in order for readers to get some understanding of the jpg?

 

[CanadianAccessUser]

Attached are two screen shots from my two reports. One is the misbehaving report and one is the working one. You can see that everything else is showing exactly how it should...
The circled stats are for the same agent / same date range.

 

[CanadianAccessUser]

all tables start with tbl and all queries start with qry

I'm the one working at the call center. lol
This database is a go to for the coaches to view the agent stats for their teams.
Quality
Telephony
Attendance
Time off
Coaches Logs
Sales
etc.

 

[jdraw]

Why are the queries in your tables and relationships?

In 3 or 4 lines WHAT is this database about in order for readers to get some understanding of the jpg?

 

[CanadianAccessUser]

There is a query in the relationships because it is limiting the agents to the active ones. That way my reports will only show the agents who still work here.
Also, qrySales and qryAbsenteeism have calculations in them that can't be calculated in a table so they are used in place of the table in the relationships.

In this call center there are many campaigns. This database is for one campaign. On this campaign there are 5 coaches. Each coach has about 10-15 agents assigned to them. The coaches ensure that the agents stats are where they need to be and provide "coaching" when required. Some of the stats are listed above (quality, etc.).
The coaches also run reports on any date range and report to the higher ups on the stats in their team.
Is there any other information you need?

 

[CanadianAccessUser]

I'm thinking it may have something to do with this error I never solved... but the error I have would only affect stats that have been formatted using my "getminuteformat" code, not the number of calls... totally confused.

I have added this error to the VBA forum in hopes that fixing it will assist with my current report issue.

 

[CanadianAccessUser]

Ok, I got help from plog and Galaxiom on my VBA issue and it's fixed but this report is still showing the same double stats on the same person. Any ideas?

 

[CanadianAccessUser]

I fixed it and there's no way you guys would have found my ridiculous error without access to my database....

Explanation:
Long store short, there was a customer survey with no score... This gave the agent two records before the sum. (one with survey scores and one without) Then it was summed showing double the stats.

I would have caught it earlier when checking my queries etc. but I had only checked the stats that were doubling, not the ones that weren't, and the issue was a stat that wasn't doubling. duh.....

All help through this has been highly appreciated.