Counting within Groups within a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report in Access03 that lists data in the following way:

Salesman (Group)
Account Class of Trade (Group)
Account Name / Number (Group)
Detail - Items in each Customer's Product Profile

Now, here's the problem...

I want to do a summary first after each class of trade. Here, I count the
number of accounts and the number of items in each customer's product profile
(displaying a total number of items for that class of trade.)

I also do a grand summary in the report footer. I count the total number of
accounts in the report and the total number of items (not individual items,
all item entries.)

I also want to do a summary after each Salesperson. So far, everything I've
mentioned - I have working correctly. In the Salesperson Group Footer, I'm
counting Class of Trade entries & Item (product) entries. I also want to
count the number of accounts in each Salesperson Group but for some reason -
I can't make it work.

The data structure in the table lists the account number, account name, item
(or product) information in each row. When an account has more than one item
in there product profile, the account name and number are listed more than
once.

I was able to make most of the count functions work by using the =1 trick.
But it seems - not matter what I try, I can't count the number of account in
each Salesperson Group - it's driving me crazy - two day's now trying to
figure this out.

Please help - I need this to work... Thx.
 
JK said:
I have a report in Access03 that lists data in the following way:

Salesman (Group)
Account Class of Trade (Group)
Account Name / Number (Group)
Detail - Items in each Customer's Product Profile

Now, here's the problem...

I want to do a summary first after each class of trade. Here, I count the
number of accounts and the number of items in each customer's product profile
(displaying a total number of items for that class of trade.)

I also do a grand summary in the report footer. I count the total number of
accounts in the report and the total number of items (not individual items,
all item entries.)

I also want to do a summary after each Salesperson. So far, everything I've
mentioned - I have working correctly. In the Salesperson Group Footer, I'm
counting Class of Trade entries & Item (product) entries. I also want to
count the number of accounts in each Salesperson Group but for some reason -
I can't make it work.

The data structure in the table lists the account number, account name, item
(or product) information in each row. When an account has more than one item
in there product profile, the account name and number are listed more than
once.

I was able to make most of the count functions work by using the =1 trick.
But it seems - not matter what I try, I can't count the number of account in
each Salesperson Group - it's driving me crazy - two day's now trying to
figure this out.
Click to expand...


Use a =1 running sum (Over Group) text box (named
txtAcctCnt) in the accounts group header/footer.

Then in the class group footer section, use a text box
(named txtRunAcctCnt) with the expression =txtAcctCnt and
RunningSum set to Over Group.

Then the Salesman group footer text box can display the
number of accounts by using the expression =txtRunAcctCnt
 
I've spent day's trying to figure this out...
Maybe if I explain it differently...

This is the report setup:

Salesperson Header
Class of Trade Header
Customer Name Header
Detail
Customer Name Footer
Total No. Accounts (display) - Working
Total No. Items (display) - Working
Sales Person Footer
Total No. Accounts (display) - Can't get this to work!
Total No. Items (display) - Working
Total No. Classes (display) - Working
Report Footer
Total No. Accounts (display) - Can't get this to work!
Total No. Items (display) - Working
Total No. Classes (display) - Working

It doesn't matter what I do, I can't get the report to count the number of
accounts by Salesperson. I enter get a running sum overall (doesn't stop at
the end of the Salesperson group) or I get the total number of accounts in
the last class of trade.

Anyone else have this problem? Why is counting in Access so difficult? Seems
like a common thing...

Any help is always appreciated...
 
First, you need a second total no accounts text box in the
Customer footer. The only difference is that this second
one should have its RunningSum property set to Over Group.

Then, the Class of Trade group **footer** section needs
another running sum text box that refers to the Customer
footer running total. Since you have no ither use for the
Class of Trade group footer, make it invisible.

Then the Salesperson footer can display its total by using a
text box that refers to the running sum text box in the
Class of Trade group footer.

Finally, add a second Salesperson footer total text box, but
with its RunningSum set to Over All. Te report footer grand
total text box would then refer to the Salesperson footer
total text box.

There should be a clear pattern emerging so you can see how
to do this for any number of levels. It's only complicated
when you don't understand it. ;-)
 
You are my new hero in life. I was able to get it working following your
advice - although, I still don't quite understand the logic - but I plan to
figure it out before I do anything else. I can't tell you how much I
appreciate your help...

Marshall Barton said:
First, you need a second total no accounts text box in the
Customer footer. The only difference is that this second
one should have its RunningSum property set to Over Group.

Then, the Class of Trade group **footer** section needs
another running sum text box that refers to the Customer
footer running total. Since you have no ither use for the
Class of Trade group footer, make it invisible.

Then the Salesperson footer can display its total by using a
text box that refers to the running sum text box in the
Class of Trade group footer.

Finally, add a second Salesperson footer total text box, but
with its RunningSum set to Over All. Te report footer grand
total text box would then refer to the Salesperson footer
total text box.

There should be a clear pattern emerging so you can see how
to do this for any number of levels. It's only complicated
when you don't understand it. ;-)
--
Marsh
MVP [MS Access]

I've spent day's trying to figure this out...
Maybe if I explain it differently...

This is the report setup:

Salesperson Header
Class of Trade Header
Customer Name Header
Detail
Customer Name Footer
Total No. Accounts (display) - Working
Total No. Items (display) - Working
Sales Person Footer
Total No. Accounts (display) - Can't get this to work!
Total No. Items (display) - Working
Total No. Classes (display) - Working
Report Footer
Total No. Accounts (display) - Can't get this to work!
Total No. Items (display) - Working
Total No. Classes (display) - Working

It doesn't matter what I do, I can't get the report to count the number of
accounts by Salesperson. I enter get a running sum overall (doesn't stop at
the end of the Salesperson group) or I get the total number of accounts in
the last class of trade.

Anyone else have this problem? Why is counting in Access so difficult? Seems
like a common thing...
Click to expand...
Click to expand...
 
Back
Top