• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 348
  • Last Modified:

Access Query / Report Calculations

I'm new to Access and need assistance in developing some reports chow totals.
Basically need to show for each Account Manager, the total number of instructions received for option A and for option B.

It should look like this:

Total Number of Clients: 30

Account Manager 1
Option A: 5
Option B: 4
Total response: 9
Total Clients: 15
Outstanding responses: 7

Account Manager 2
Option A: 2
Option B: 1
Total response: 3
Total Clients: 15
Outstanding responses: 12

Can anybody help out with guides to how I manage this type of Reporting?

  • 2
  • 2
1 Solution
Barry CunneyCommented:
Do you have a table that already stores data relating to the above?

First create a query to the ReportSource
This query will possibly have to be something like:

,SUM(Response) [Total Response]
,SUM(Clients) [Total Clients]
FROM [Client_Instructions]

Save this query - this query will be the reportsource for your report
Put the account manager field in a Account Manager header section  and the option field in the detail section of a report
Put Total Responses and Total Clients in a a Account Manage Footer section on the report
dnt2009Author Commented:
I've attached a sample file.
Jeffrey CoachmanMIS LiasonCommented:
Questions remain...
What are you considering a "response" and "Outstanding Response"?
There is nothing named "response" or "Outstanding" in your sample database...?

You should take a close look at your design here, as there are a few aspects that will cause you frustration in the future.
1. You should not be linking the tables on Manager name or Client names.  These "names" can be brought in by a query, ...you should only be linking on the ID's
2. You should consider adding a "Options" table.  This is for two reasons: 1, it will allow you to make a dropdown box for the options
3. Consider dropping the word "Option" from the values, this is redundant, as the name of the Filed is "CilientOptions"

I made modification 1 already

Sample is attached.
But note here that the creation of this report is not something that is easy to explain if you are new to Access report design.
You will have to examine what I have done here closely and try to adapt it to work in your database.

dnt2009Author Commented:
Thanks Jeff. It took me aa while to figure out how to do these calculations but I've been able to replicate the model for other calculations.
Jeffrey CoachmanMIS LiasonCommented:
Great, ...glad I could help.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now