Counting in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a report where I want to count the number of orders physicians
have placed during a given time period. I want a summary by physician, for
example:

Smith 8
Jones 9

Total 17

I have not set it up correctly, because it is showing the total number for
every physician (in the above example, 17 on every physician). How do I get
it to sum for each physician and total at the bottom of the report?

Thank you!
 
Depending on wether you just want the counts or the details as well.
In the First case
create a query like this & use it as the Base of the Report

SELECT A.PHYSISCIAN, COUNT(*) As TheCount FROM ORDERS A
GROUP BY A.PHYSISCIAN

Put a Control with the ControlSource: =Sum(TheCount) in the Report's Footer

Seccond Case

SELECT A.* FROM ORDERS A
ORDER BY A.PHYSISCIAN

Create a Group Footer for Physiscian

Add a Hidden Control (name it: RunSum) in the detail section & Set it's Sum
property to 'over group' & the ControlSource : =1
In the Group footer place a control with the ControlSource: =[RunSum]

HTH

Pieter

More about basic report design & much, much more at
http://www.mvps.org/access
 
Thank you for your response. I am a novice at access, and only do basic
queries. Can you tell me where exactly how I would create the query to do
this? Can I use my existing query that only pulls the physician names and
dates of orders, or do I create an entire new one? where do I put the SELECT
A.PHYSISCIAN, COUNT(*) As TheCount FROM ORDERS A GROUP BY A.PHYSICIAN? Then
where do I put the other info in the base? Sorry, I have learned a lot, but
obviously still learning!

Thanks so much for your help!
 
Yes, You can work with your current query
Change the Query view to SQL - in query design: View Menu, SQL View - or the
leftmost button on the query toolbar

HTH

Pieter
 
Back
Top