Count Report 1

[Celeste025]

I am using Access 2000 in WinXP. I have a database created that tracks sales calls. Agents submit "sheets" that can have 4 different types of calls: purchasing; sales; operations; and jobber. The first 3 types can only have 1 call per sheet, but jobber can have up to 5 calls per sheet. There can be any combination of calls on each sheet. Each sheet is assigned a tracking number that applies to all of the calls on that sheet.

I currently have a detail report with subreports that links the tracking numbers of the different call types and puts all of the calls into one report. I am using a main query that has a Between statement for the date so I can run for specific ranges and Like statements for agent info and customer info so I can pull the report for specific criteria. The actual call info is in 4 different queries, one for each call type. The information for the call types is similar and it gets big and confusing if I put it all into one query.

What I need is a summary report that will give the total calls for the specific date range, the calls per agent, agency or to a customer. I should be able to see the total calls, and the calls per type. I'd like to see a report that looks something like this:

Calls for January - 200
Sales calls - 20
Purchasing Calls - 20
Operations calls - 20
Jobber calls - 140

Calls from XYZ Marketing - 50
Calls from Jim Smith, XYZ - 20
Calls from Joe Green, XYZ - 15
Calls from Jerry Brown, XYZ - 15

Calls from ABC Marketing - 50
etc...

Calls to Ajax Customer - 38
Calls to Acme Customer - 56
etc...

I have seen something done with a calculated field placed in a footer that will count the calls in the detail but Im unsure about where to begin with this. Id appreiciate any assistance.

Thanks

 

[tizwaz]

you could put something like this in a text box in a report footer
= sum(IIF([call]="Jim Smith",1,0))
This should count any calls from Jim Smith. Obviously change [call] to the name of your field

 

[CosmoKramer]

It looks like your report would have three basic sections (types of calls, calls from, and calls to). I would create three individual reports and then insert these subreports into one main report.

I've done this a lot, and when formatted and lined up properly, the end result looks seamless to the end user.....

 

[Celeste025]

Cosmo,

Thats how I have the main detail report setup and it does work seamlessly. However, when I tried to do this just for a detail with the count function as the only thing in the three subreports, I have a problem with sorting and linking. The detail report was linked on Tracking Number and when the count report is linked on Tracking Number, it gives me the total calls for each subreport per tracking number - not very efficient. I tried linking it on Agency and it didn't work.

Any suggestions on how to link these correctly?

 

[CosmoKramer]

Correction...

It looks like your report would have three basic sections (types of calls, calls from, and calls to). I would create three individual reports and then insert these as subreports into one main report.

I've done this a lot, and when formatted and lined up properly, the end result looks seamless to the end user.....

And, it if the subreports are grouped correctly, you wouldn't have to hard-code those data elements.....

 

[Celeste025]

Right, as I said, thats how Ive got the detail report setup. However I cant seem to figure out how to group the count report properly.

 

[CosmoKramer]

You shouldn't have to do any linking at all. The main report would be an empty shell, and you can insert each subreport into it.

Each subreport should be a report that could stand on its own. You can suppress everything but the group footer, and have the group footer hold your total field and label.

 

[Celeste025]

Thanks Cosmo! I finally got it working once you got me on the right track. The main problem I was having was a query problem. My db is setup with a table for "Sales", "Purchasing", etc. and I was trying to count the field named "Tracking Number" from each of them, which was causing confusion. I finally made one query that just had the customer/agent information that I needed along with the Tracking Number fields from each of the 4 tables. Once I was able to properly name each field (ie Sales_Tracking Number) I was home free. I ended up creating 4 separate report; one that counts call totals; one that counts call totals by Agency; one that counts call totals by Agent; and one that counts call totals by Customer. My boss loves it!

Thanks again