Create a Summary Report - with multiple data elements 1

[marie515]

Hi there.

I want to create a report that shows the following:

PRODUCTION: WEEKLY/MONTHLY/QUARTERLY
__________________________________________________________
|THIS WEEK | THIS MONTH |THIS QUARTER| Q1|Q2|Q3|Q4
CSR1 | 33 | 22 | 135 | 135 88 98 58
CSR2 | 22 | 15 | 110 | 110
ETC....

I also want to include information on # of cases Received/Closed per quarter.

RECEIPTS/CLOSED: WEEKLY/MONTHLY/QUARTERLY
__________________________________________________________
|THIS WEEK | THIS MONTH |THIS QUARTER| Q1|Q2|Q3|Q4
Received| 33 | 22 | 135 | 135 88 98 58
Closed | 22 | 15 | 110 | 110


In order to get the summarized numbers of cases closed per CSR, I did a separate query off the main menu to get the count for each of the numbers above. I then pulled all of the queries into one query to base the report on.

The problem is that Access will only hold (I think) 25 queries and I'd need about 40 in order to show all the data I want to show in this report.

Can you tell me the best way in which to get the numbers I want? Would you recommend I use a "text Box" and in the "control source" use the Expression Builder?

Any suggestions would be welcome.

Thank you.

 

[NorthNone]

How about subreports? I've used these with great success for such reports. Each subreport has its own query, then one main report holds all the subreports.

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[marie515]

Thanks! Yes, I'll do that. I'll use subreports.

 

[NorthNone]

Glad I could be of assistance.

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[dhookom]

You can also create a totals query that returns the correct totals in the columns. For instance if you had fields like [Product], [SaleDate] and [QtySold] and you wanted columns like you noted above:
Create a totals query that groups by [Product]. For the this week column, use an expression:
ThisWeek: Sum(Abs(Format(Date(),"yyyyww") = Format([SaleDate],"yyyyww")) * [SaleDate])
ThisMonth:Sum(Abs(Format(Date(),"yyyymm") = Format([SaleDate],"yyyymm")) * [SaleDate])

Other date interval sales columns can be created using similar expressions. No subreports required.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[NorthNone]

Marie, Duane's idea is much better and more reliable than my subreports idea. I didn't read your posting as carefully as I should have before responding :-(

---------------------------------------
The customer may not always be right, but the customer is always the customer.

 

[marie515]

Thanks very much.

Duane - I'm confused (which is not unusual). In my data, I have the following:

* CSR_Name
* Receipt_Date

Now, let's say I want to know how many calls "Sharon" took for the current week or current month or current quarter, can you give me an example of how I would code that? I don't understand how to code it with your example.

Thanks!

 

[marie515]

Duane,

I'm a bit confused (which is not unusual). Can you tell me how I would get the following using your coding?

Field Names:

* CSR_Name
* ReceiptDate

Let's say, I want to know how many calls "Sharon" took this week, this month, this quarter....how would I code that in a query?

I can't see in your example where you would indicate a specific person's work completed.

Any idea?

Thank you.

 

[dhookom]

Create a totals query that groups by [CSR_Name]. For the this week column, use an expression:
ThisWeek: Sum(Abs(Format(Date(),"yyyyww") = Format([ReceiptDate],"yyyyww")) )
ThisMonth:Sum(Abs(Format(Date(),"yyyymm") = Format([ReceiptDate],"yyyymm")) )

These expressions will count the number of calls per time period by CSR_Name.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.