Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Distinct Count in Second Group Footer

Distinct Count in Second Group Footer

The running sum is working great at the employee level and is totaling correctly in that group footer, but I need to sum the distinct employee sales transactions in the department footer, as well. Here's what I have:

Name: txtEmployees
Control Source: =1
Running Sum: Over Group
Visible: No

Name: txtTotalEmployees
Control Source: =txtEmployees
Running Sum: No
Visible: Yes

Employee 1           Sales: 15
Employee 2           Sales: 10
Employee 3           Sales: 2
Accounting Total: 2

Employee 21           Sales: 9
Employee 37           Sales: 6
Employee 30           Sales: 4
IT Total: 6

Employee 24           Sales: 10
Employee 64           Sales: 14
Employee 32           Sales: 17
Marketing Total: 23

RE: Distinct Count in Second Group Footer

Where do your current department totals come from? I would expect them to be 3, 3, and 3. Do you really want a "to sum the distinct employees" rather than "to count the distinct employees"?

I'm having trouble figuring out your displayed numbers as well as what you are asking for.

Hook'D on Access
MS Access MVP

RE: Distinct Count in Second Group Footer

Thanks for the response. "I need to sum the distinct employee sales transactions in the department footer".

What you see is the Employee footer. Employee 1 had 15 total sales, Employee 2 had 10 total sales, etc. One sale could be 1-5 rows in the table, but I only need to capture the number of distinct sales ID. And this is working properly at the Employee group.

What I need is to sum all the sales for each department (Accounting Total = employee 1 sales + employee 2 sales + employee 3 sales, etc.)

So what the totals should read is Accounting Total: 27, IT Total: 19, Marketing Total: 41

What I'm currently getting is a sum of only the last value of each group instead of the whole group.

RE: Distinct Count in Second Group Footer

I don't understand why you are using "distinct" in your request. Don't you just want to sum the sales by department?

I would expect you could use the same expression you have in the employee Sales text box but wrap it in =Sum(...). There would be no need for running sums.

Hook'D on Access
MS Access MVP

RE: Distinct Count in Second Group Footer

How do I show that Accounting had 2 total sales?

Item Sold	Invoice #   Amount	  Name	           Dept
Software	  001	    $35.00	John Doe	Accounting
Server	          002	    $50.00	Bill Smith	    IT
Server	          001	    $20.00	John Doe	Accounting
Hardware	  003	    $17.00	Mary Walker	Accounting
Software	  004	    $32.00	Ron Johnson	Marketing
Desk	          003	   $398.00	Mary Walker	Accounting
Software	  005	   $121.00	Vince Thompson	    IT

RE: Distinct Count in Second Group Footer

Your use of "total sales" is confusing. Also, none of you data seem to line up with previous posts. Total Sales to me would sum the amounts. Looking at your data, I assume you are counting the unique invoices by department.

If I am correct about unique invoices by dept I would create a query that counts unique invoices.

CODE --> sql

SELECT Dept, Count(*) as UniqueInvoices FROM
(SELECT Dept, [Invoice #]
FROM tblMTown
GROUP BY Dept, [Invoice #]) Q

Dept        UniqueInvoices
Accounting       2
IT               2
Marketing        1

Then, add this query to your report's record source and join on the Dept fields. You can then display the UniqueInvoices to your report.

Hook'D on Access
MS Access MVP

RE: Distinct Count in Second Group Footer

Finally worked, thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close