Query showing up with strange #

[saraann422]

I'm using the query below and the PhoneTime results do not not show up correctly. If I run the query for one day, the results are correct, if I run it for 2 days the result is twice what it should be, if I run it for 3 days the result is 3 times what it should be, etc. Does anyone know how to fix this?

Thanks!
Sarah

SELECT [Clerk - Closed & Promoted to QC].Clerk, Sum(([Clerk - Closed & Promoted to QC]!CC)) AS CC, Sum((([Clerk - Closed & Promoted to QC]![Promoted to QC]))) AS Promoted, Sum(([Clerk - Closed & Promoted to QC]!EM+[Clerk - Closed & Promoted to QC]!EQ+[Clerk - Closed & Promoted to QC]!RR+[Clerk - Closed & Promoted to QC]!QQ+[Clerk - Closed & Promoted to QC]!T1+[Clerk - Closed & Promoted to QC]!T2+[Clerk - Closed & Promoted to QC]!T3+[Clerk - Closed & Promoted to QC]!T5+[Clerk - Closed & Promoted to QC]!T7+[Clerk - Closed & Promoted to QC]!WR)) AS [Priority Mail], Sum(([Clerk - Closed & Promoted to QC]!CI+[Clerk - Closed & Promoted to QC]!DD+[Clerk - Closed & Promoted to QC]!FS+[Clerk - Closed & Promoted to QC]!MD+[Clerk - Closed & Promoted to QC]!MM+[Clerk - Closed & Promoted to QC]!PA+[Clerk - Closed & Promoted to QC]!RV+[Clerk - Closed & Promoted to QC]!SS+[Clerk - Closed & Promoted to QC]!TA+[Clerk - Closed & Promoted to QC]!WW)) AS [General Mail], (SELECT Count([QC Information].[QC'er]) FROM [QC Information] WHERE ((([QC Information].[QC'er])=[Clerk - Closed & Promoted to QC].[Clerk]) AND (([QC Information].[Today's Date])>=[StartDate] And ([QC Information].[Today's Date])<=[EndDate]) )) AS QCd, (SELECT Count([QC Information].[Analyst]) FROM [QC Information] WHERE ((([QC Information].[Analyst])=[Clerk - Closed & Promoted to QC].[Clerk]) AND (([QC Information].[Today's Date])>=[StartDate] And ([QC Information].[Today's Date])<=[EndDate]) AND (([QC Information].[Case Status])="Expedite/Re-QC" Or ([QC Information].[Case Status])="Expedite" Or ([QC Information].[Case Status])="Re-QC" Or ([QC Information].[Case Status])="New"))) AS wasQCd, (SELECT (Sum(([Attendence]!Hours*60)+[Attendence]!Minutes)/60) FROM [Attendence] WHERE (([Attendence].[Date of Absence])>=[StartDate] And ([Attendence].[Date of Absence])<=[EndDate]) AND ([Attendence].Employee)=[Clerk - Closed & Promoted to QC].[Clerk]) AS Hours1Away, (SELECT (Sum(([CYA - RA]!Hours*60)+[CYA - RA]!Minutes)/60) FROM [CYA - RA] WHERE (([CYA - RA].[Date of Absence])>=[StartDate] And ([CYA - RA].[Date of Absence])<=[EndDate]) AND ([CYA - RA].Employee)=[Clerk - Closed & Promoted to QC].[Clerk]) AS Hours2Away, IIf([Hours1Away]>0,(IIf([Hours2Away]>0,([Hours1Away]+[Hours2Away]),[Hours1Away])),[Hours2Away]) AS HoursAway,

Sum(([Daily Call Stats]!StaffedTime)) AS PhoneTime

FROM [Clerk - Closed & Promoted to QC] INNER JOIN [Daily Call Stats] ON [Clerk - Closed & Promoted to QC].Clerk = [Daily Call Stats].[Agent Name]
WHERE ((([Clerk - Closed & Promoted to QC].Date)>=[StartDate] And ([Clerk - Closed & Promoted to QC].Date)<=[EndDate]) AND (([Daily Call Stats].Date)>=[StartDate] And ([Daily Call Stats].Date)<=[EndDate]))
GROUP BY [Clerk - Closed & Promoted to QC].Clerk;

 

[GingerR]

Your kidding?

If I were you I'd get rid of calculations and such one at a time and see if it ever works properly. Or start over and do one thing at a time and run the query after eact thing (column, field, calculation) you add and see when it starts doing something you don't want it to do. Without knowing your table structure or any of your data or what the results are or what you want the results to be, it's pretty hard to help you.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[saraann422]

Every part of the query, except the PhoneTime works correctly. It's really weird.

 

[GingerR]

It's not "wierd", it's how you have it programmed. You're summing up something and if it's two or three times what you expect it to be, then you are getting two or three times the records you need.

make it a SELECT query and get rid of columns until you can see what records are being duplicated or tripled. that should give you a clue as to how your joins are causing you trouble.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html