By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,455 Members | 2,161 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,455 IT Pros & Developers. It's quick & easy.

Obtaining sub-totals within a report grouping

patjones
Expert 100+
P: 931
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
Dec 29 '08 #1
Share this Question
Share on Google+
6 Replies


patjones
Expert 100+
P: 931
I solved my own problem! In the Control Source settings for the respective text boxes on the report, I put

Expand|Select|Wrap|Line Numbers
  1.  =DCount("[Check Disp Code]","tblMain319","[Check Disp Code] = 'O' AND [Payroll Distribution Code] = [txtPDC1].Text")
and

Expand|Select|Wrap|Line Numbers
  1.  =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! :-)
Dec 29 '08 #2

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 30 '08 #3

patjones
Expert 100+
P: 931
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
Dec 30 '08 #4

nico5038
Expert 2.5K+
P: 3,072
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)
Dec 30 '08 #5

patjones
Expert 100+
P: 931
It worked like a charm. Thanks for the guidance!

Pat
Dec 30 '08 #6

nico5038
Expert 2.5K+
P: 3,072
Well done, no thanks needed.
I'm always glad to see people learning new tricks :-)

Success with your application !

Nic;o)
Dec 30 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.