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?

Thanks,
dnt
dnt2009Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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:

SELECT
AccountManager
,Option
,SUM(Response) [Total Response]
,SUM(Clients) [Total Clients]
FROM [Client_Instructions]
GROUP BY
AccountManager
,Option

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
0
dnt2009Author Commented:
I've attached a sample file.
Sample-File.accdb
0
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...?

Also:
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.
;-)

JeffCoachman
Sample-File.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
Jeffrey CoachmanMIS LiasonCommented:
Great, ...glad I could help.

;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.