Distinct Count in Second Group Footer

[mtownbound]

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
[pre]Employee 1 Sales: 15
Employee 2 Sales: 10
Employee 3 Sales: 2
[/pre]Accounting Total: 2

IT
[pre]Employee 21 Sales: 9
Employee 37 Sales: 6
Employee 30 Sales: 4
[/pre]IT Total: 6

Marketing
[pre]Employee 24 Sales: 10
Employee 64 Sales: 14
Employee 32 Sales: 17
[/pre]Marketing Total: 23

 

[dhookom]

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.

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

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.

 

[dhookom]

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.

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

How do I show that Accounting had 2 total sales?

[pre]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
[/pre]

 

[dhookom]

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.

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

[pre]
Dept UniqueInvoices
Accounting 2
IT 2
Marketing 1
[/pre]

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.

Duane
Hook'D on Access
MS Access MVP

 

[mtownbound]

Finally worked, thanks!