Help with Productivity Sum??

[Damonj]

Hi All,

Access 2003 on WinXP Pro

I have a query named qryProductivity. This query is based
on a table that tracks the type and quantity of motor
repairs a department services. The fields are;

DATE AU SAU CU WAR TEST EST TECH
1/15/03 1 STEVE
1/15/03 8 TOM
1/16/03 4 STEVE
1/31/03 5 TOM

What I would like to do is to be able to get a sum of
AU+SAU+CU for each TECHNICIAN at the end of each month
and print it out on a report.

Example;
At the end of Jan 03
STEVE- AU+SAU+CU = 5
TOM- AU+SAU+CU = 13

TIA for any help,
Damon

 

[Dirk Goldgar]

Hi All,

Access 2003 on WinXP Pro

I have a query named qryProductivity. This query is based
on a table that tracks the type and quantity of motor
repairs a department services. The fields are;

DATE AU SAU CU WAR TEST EST TECH
1/15/03 1 STEVE
1/15/03 8 TOM
1/16/03 4 STEVE
1/31/03 5 TOM

What I would like to do is to be able to get a sum of
AU+SAU+CU for each TECHNICIAN at the end of each month
and print it out on a report.

Example;
At the end of Jan 03
STEVE- AU+SAU+CU = 5
TOM- AU+SAU+CU = 13

TIA for any help,
Damon

I'd guess this SQL would do it:

SELECT
Technician,
Sum(Nz(AU,0)+Nz(CAU,0)+Nz(CU,0)) AS Total
FROM
qryProductivity
GROUP BY Technician;

That's assuming that qryProductivity already selects only the records
for the desired month.

 

[Damonj]

Thanks Dirk!

I'll give it a try now.
Damon

-----Original Message-----


I'd guess this SQL would do it:

SELECT
Technician,
Sum(Nz(AU,0)+Nz(CAU,0)+Nz(CU,0)) AS Total
FROM
qryProductivity
GROUP BY Technician;

That's assuming that qryProductivity already selects only the records
for the desired month.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.