Access XP report sum calculation

Question

I am adding to a feedback database used within a call centre that handles a variety of services.

Part of what needs to be added is a report showing the total entries for each service that a particular CSO (customer service officer) does. When information is input, it is through a form that includes 2 drop downs - one for the CSO name, and one for the Service the entry will be relevant to.

I'd like to be able to run a report through a query where the CSO name and Service will be entered along with a date period, and the output would be a report that includes a total for each service (not just the one entered) that the CSO has received feedback for.

I hope this makes sense, and i'm sure there is a very simple solution or formula!

im trying to fit it into a report i already have with the following SQL code in its query:

SELECT [Query Main].[Entry No], [Query Main].[Log Entry Date], [Query Main].[Log Entry Time], [Query Main].[Daily Feedback].[CSO Name], [Query Main].[CSO Team], [Query Main].[Reference Number(s)], [Query Main].Address, [Query Main].[Feedback Comments], [Query Main].[Customer?], [Query Main].[Back Office?], [Query Main].Specify, [Query Main].[Feedback done by], [Query Main].[Feedback date], [Query Main].[CSO Comments], [Query Main].Agents.[CSO Name], [Query Main].Service, [Query Main].[Date of Error], [Query Main].[Entered By], [Query Main].[Is this CSO Feedback?]  
FROM [Query Main]  
WHERE ((([Query Main].Service)=[Enter Service Name]) AND (([Query Main].[Date of Error]) Between [Enter beginning date (dd/mm/yy)] And [Enter ending date (dd/mm/yy)]) AND (([Query Main].[Is this CSO Feedback?])="Yes" Or ([Query Main].[Is this CSO Feedback?]) Is Null));`

if you want all the Services returned where there is feedback, why do you need it in your selection? — SeanC
thank you for your reply, well in the reports i have at the moment it shows what was fedback and details such as the date and CSO name and service etc aswell as the outcome of the feedback. but id like to create a breakdown where between two date ranges for the named CSO the report will show how much total feedback we've had for each service they deal with. basically to see if they are getting too much feedback with a particular service and need more training for it etc. i expect it may involve some simple SQL coding to do this and im pretty useless where that is concerned im afraid! — Kris

SeanC SeanC · Accepted Answer · 2012-06-27T14:06:30

This will give you a starting point - You can use a Totals query if you don't want to delve into the SQL itself

SELECT CSO,Service,Count(Feedback) 
FROM MyData 
WHERE FeedbackDate between DateStart and DateEnd
   AND CSO=[CsoName]
GROUP BY CSO,Service;

I would recommend looking into learning the SQL, even if its simply for the case that it may be quicker to type than to drag and drop on the query builder

Side note: Its been so long since I used the query builder, I had to write the SQL first, and see what buttons got activated when I changed the view

the SQL Explained:

SELECT CSO,Service,Count(Feedback) - What do you want to return. In this case, the CSO, the service and a count of the feedback. How it decides what feedback to count comes later in the SQL
FROM MyData - What table (or query) we want to get the info from
WHERE FeedbackDate between DateStart and DateEnd AND CSO=[CsoName] - How we want to select the data
GROUP BY CSO,Service; this is how the Count is decided - in this case, we group all the CSOs and Services together, and then count the number of feedbacks found for each group.

first the query will get a list of all the CSO,Service, and Feedback, based on the restriction we have of the date range and which CSO we are interested in.
Then it will put all the CSOs and Services that are the same, and count the number of feedbacks it finds.
Finally, it will give you this list back, so you will end up with something like:

CSO   Service       CountOfFeedback
FRED  Open account  11
FRED  Close Account 2
FRED  Send Order    47

Access XP report sum calculation

1 Answers