469,167 Members | 2,155 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Obtaining sub-totals within a report grouping

931 Expert 512MB
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!

Dec 29 '08 #1
6 1319
931 Expert 512MB
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")

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
3,080 Expert 2GB
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
to get the desired result.

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

Dec 30 '08 #3
931 Expert 512MB
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.

Dec 30 '08 #4
3,080 Expert 2GB
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!

Dec 30 '08 #5
931 Expert 512MB
It worked like a charm. Thanks for the guidance!

Dec 30 '08 #6
3,080 Expert 2GB
Well done, no thanks needed.
I'm always glad to see people learning new tricks :-)

Success with your application !

Dec 30 '08 #7

Post your reply

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

Similar topics

4 posts views Thread by middletree | last post: by
reply views Thread by Lennart Hoglund | last post: by
11 posts views Thread by seannakasone | last post: by
2 posts views Thread by yoav.benyosef | last post: by
7 posts views Thread by =?Utf-8?B?Sm9obiBTdGFnZ3M=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.