Help with totals in Access Report (Access 2003)

[Sephiroth0327]

Hi All,

I do a lot of work in Access in regards to SQL, queries and linked tables, but have never gotten into Reports too much. I have created a basic report which pulls from a "Calls by Day" table. This table contains data pertaining to phone calls my department has received by day.

My basic report should display a summary of the # of calls we received by day.

Click link to access screenshot of Design View - http://www.flickr.com/photos/sephiroth0327/4396126487/sizes/o/

The resulting report does correct show each day that calls were received, but the total column just displays the total number of calls for all days, rather than the total for the day in question. I know the problem is with how I wrote the Count function, but I'm not sure how to fix it.

Essentially, I want to see:

February 25, 2010 - 1
February 26, 2010 - 9

but what I am seeing is

February 25, 2010 - 10
February 26, 2010 - 10

I know this is simple so please be gentle! Thanks!

 

[vbaInet]

If you want to count based on a certain criteria then you should be looking to use the DCount() function. Look into that.

 

[Sephiroth0327]

Thanks for the quick post. I was originally using DCount, but I couldn't get it to work correctly. I just want to count the records associated with a given created date (I am using the [Created] field here).

I'll take another stab with the DCount. I must have missed something.

 

[vbaInet]

Another idea is to create a query that does the counting for you, and create a report based on that query.

By the way, maybe you were missing the WHERE argument of the DCOUNT().

 

[Rich]

Add a Group to the Report and Group by day, put the the count function in the Group footer