Counting in a report

[dwest1]

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!

 

[Pieter Wijnen]

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

 

[dwest1]

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!

 

[Pieter Wijnen]

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