Obtaining sub-totals within a report grouping

[patjones]

Good afternoon:

This seems like it shouldn't be hard, and then again this is how so many problems seem at the outset. My situation is this:

I have a report called rptMain319, which is based on a table tblMain319. The report groups employees by their work location ([Payroll Distribution Code]). By putting a Count(*) function in the footer for the Payroll Distribution grouping, I can get the total number of employees in that particular work location.

My problem is that, in each work location grouping, I need to sub-total the number of employees who get checks and the number who get direct deposit ([Check Disp Code] = 'O' or = 'E' respectively).

Now, I know I could do this by creating a sub-grouping on [Check Disp Code] within [Payroll Distribution Code], but that gives me two separate lists within the [Payroll Distribution Code] grouping - which I don't want. I simply want one list, with the two numbers shown at the bottom of the list.

Any ideas? Thanks so much!

Pat

[patjones]

I solved my own problem! In the Control Source settings for the respective text boxes on the report, I put

 =DCount("[Check Disp Code]","tblMain319","[Check Disp Code] = 'O' AND [Payroll Distribution Code] = [txtPDC1].Text")

and

 =DCount("[Check Disp Code]","tblMain319","[Check Disp Code] = 'E' AND [Payroll Distribution Code] = [txtPDC1].Text")

where txtPDC1 is just another text box on the report that happens to hold the Payroll Distribution Code for the work location that is being represented on a particular page.

Thanks anyway for all the potential help! :-)

[nico5038]

I would have added two fields to the report query like:

SELECT iif([Check Disp Code] = 'O',1,0) AS DispO, iif([Check Disp Code] = 'E',1,0) AS DispE, ...

Now in the report you can place two fields with a sum like:
=Sum(DispO) and
=Sum(DispE)
to get the desired result.

It's faster this way as a Dcount (like all "D" functions) is rather slow in processing.

Nic;o)

[patjones]

Thanks for your suggestion Nico.

I know that there is a big performance hit with those 'D' functions. Currently, this report has as it's record source table tblMain319. I'm not defining the report's source with SQL - but perhaps I should play around around with it a little more in VBA and work it so that your 'IIf' method works.

I'll let you know how it turns out. Thanks again for the suggestion.

Pat

[nico5038]

Hi Pat,

There's little difference for Access between a "table referenced" report and a query bases report, as in both cases a SELECT will be performed.
Just open the report in design mode and activate the recordsource property by pressing the [...] button. Access will ask or the query editor needs to be started and after confirmation you can add the IIF's.
You can save the created query (using the menu) and you'll notice that the record source is pointing to that newly created query. When you save just the created query by closing it, Access will store the SQL statement instead.

So no VBA code needed :-)

Success with your application!

Nic;o)

[patjones]

It worked like a charm. Thanks for the guidance!

Pat

[nico5038]

Well done, no thanks needed.
I'm always glad to see people learning new tricks :-)

Success with your application !

Nic;o)