×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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

(OP)
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

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

Accounting Total: 2

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

IT Total: 6

Marketing
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.

### RE: Distinct Count in Second Group Footer

(OP)
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.

### RE: Distinct Count in Second Group Footer

(OP)
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
GROUP BY Dept 

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.

### RE: Distinct Count in Second Group Footer

(OP)
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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!