Group and Count based on Field Population

[Freefall27]

I am running Access 2007 and need to provide a report based on query below. I have customers from the same area that are assigned to certain equipment. I need to build a report which groups the assigned equipment, counts customers (assigned to equipment), and provides overall total. I have provided the query result and desired report result below. If more information is needed please let me know.

Query Result
YYYYMM, Area, Customer, Total, Assigned Equipment,
201204, A Town, Joe Smith , 200, ABC,
201204, A Town, Bill Davis, 400, BAC,
201204, A Town, Al Johnson, 300, ABC,
201204, A Town, George Decker, 800, CAG,
201204 B Town, Hal Eckhardt, 600, RST,
201204 B Town, John Johnson, 150, SGV,
201204 B Town, Cindy Ally, 300, RST,
201204 B Town, Debbie Jones, 200, RST,


Desired Report Result
YYYYMM, Area, Customer, Total, Assigned Equipment
201204, A Town, 2 Customers, 500, ABC
201204, A Town, 1 Customers, 400, BAC
201204, A Town, 1 Customers, 800, CAG

201204, B Town, 3 Customers, 1100, RST
201204, B Town, 1 Customers, 150, SGV

 

[dhookom]

Does this work for you?

SELECT YYYYMM, AREA, COUNT(*) & " Customers" as Customers, Sum(Total) as Sum, [Assigned Equipment]
FROM [Query Below]
GROUP BY YYYYMM, AREA, [Assigned Equipment];

Duane
Hook'D on Access
MS Access MVP

 

[Freefall27]

dhookom that works perfectly!
One additional issue that I need to account for is when the assigned equipment is blank. I will need to incorporate this into the SQL statement.


YYYYMM, Area, Customer, Total, Assigned Equipment,
201204, A Town, Joe Smith , 200, ABC,
201204, A Town, Bill Davis, 400, BAC,
201204, A Town, Al Johnson, 300, ABC,
201204, A Town, George Decker, 800, CAG,
201204, A Town, Frank Smith, 800, blank,
Desired Report Result
YYYYMM, Area, Customer, Total, Assigned Equipment
201204, A Town, 2 Customers, 500, ABC
201204, A Town, 1 Customers, 400, BAC
201204, A Town, 1 Customers, 800, CAG
201204, A Town, 1 Customers, 800, No Assignment

 

[dhookom]

Try:

SELECT YYYYMM, AREA, COUNT(*) & " Customers" as Customers, Sum(Total) as Sum,
 Nz([Assigned Equipment],"No Assignment") as Equipment
FROM [Query Below]
GROUP BY YYYYMM, AREA, Nz([Assigned Equipment],"No Assignment");

Duane
Hook'D on Access
MS Access MVP

 

[Freefall27]

Works just as required. Thank You!