Summary Report Totals 1

[MrWilly]

Can anyone point me in the right direction here?

I am trying to create a Detailed Analysis report that will display summaries (counts) of all types of calls received in the last week, month, quarter etc...

Having worked mostly with Crystal Reports formulae, my brain isn't switching gears to Access...I'm trying to figure out how to do a conditional count, based on the vaule of a field.

Here's an example:
There are 10 records returned by the query the report is based on. Of those ten records, 5 are Password Related, 3 are Browser Related and 2 are UE Related.

I have 3 unbound text boxes in the detail section of the report with the following folmulae:
=Count([Standard_Issues]="Password Related&quot
=Count([Standard_Issues]="Browser Related&quot
=Count([Standard_Issues]="UE Related&quot

Obviously this is not working as expected, as each box is returning a count of 10.

Any assistance is always appreciated!

Happy New Year - Cheers!

Willy

p.s. using Access 2000

 

[PWise]

try
=dCount("Standard_Issues","issuestable","Standard_Issues='Password Related'&quot
=dCount("Standard_Issues","issuestable","Standard_Issues='Browser Related'&quot
=dCount("Standard_Issues","issuestable","Standard_Issues='UE Related'&quot

 

[MrWilly]

Thanks pwise!

It is now returning the count of Password Related records for the entire table, except I'm looking to have it count only those records for the specific time period.

Currently, I have the text boxes containing the formulae in the footer of the Date_Time By Month group.

Report looks like this:
***************************
April 2002
Password Related Calls: 121
Browser Related Calls: 134
UE Related Calls: 106

May 2002
Password Related Calls: 121
Browser Related Calls: 134
UE Related Calls: 106
****************************
It should look like:
****************************
April 2002
Password Related Calls: 18
Browser Related Calls: 13
UE Related Calls: 19

May 2002
Password Related Calls: 7
Browser Related Calls: 34
UE Related Calls: 29
etc...
****************************

Can you help?

 

[PWise]

=dCount("Standard_Issues","issuestable","Standard_Issues='Password Related' and " & month(date)& "=" & 4 & Year(date)& "=" & 2002)' for april 2002

 

[PWise]

On second thought why don’t you group by month and related field and put count text box in related field footer.

 

[MrWilly]

Thanks Again pwise!

I'll try it out today and post my results...

 

[MrWilly]

OK pwise, I'm still having a bit of trouble in sorting out the monthly totals.

My report is structured as follows:

Report Header
Page Header
Date_Time by Month Header
Details - supressed
Date_Time by Month Footer
unbound text boxes with the
=dCount "Standard_Issues","issuestable","Standard_Issues='Password Related') formula

Page Footer
Report Footer


Even though I have the report grouped by Month, and the formula box in the footer of that group - it is coming back with the total for the table, not the month.

I did try the expanded formula you offered in your reply, but ran into syntax errors (Invalid String)

Is it possible to enter a SQL statement into the Expression Builder that would return a summary total based on the Group?

If you can assist further, I'd really appreciate it.

Thanks!

 

[Celeste025]

You might try adding a between statement to your query. When I need to run reports for different date periods, I do it in my query with "Between [Enter Beginning Date] and [Enter Ending Date]" in the date criteria. Of course this wouldn't print multiple months, but I use it to print detail call reports by month.