DESPERATE NEED HELP ASAP Totaling seperate fields in one query

[Guest]

I'm trying to use the below query in a report that is using several queries.
I need to add up how much time they spent doing each of the following [C/R
G/I B/GCheck], [DatabaseCheck], [NAFTA Check],[Referral Check], [Billing
Record Check], [A/O] and [Electronics] but I can't get it to total each of
them seperately.


SELECT PDIG.[Date Received], PDIG.[Associate Last Name], PDIG.[Associate
First Name], Sum(PDIG.[Time Spent]) AS [SumOfTime Spent], -Sum(PDIG.[C/R G/I
B/G Check]) AS CrGiBgChecked, -Sum(PDIG.[Database Check]) AS DatabaseChecked,
-Sum(PDIG.[NAFTA Check]) AS NAFTAChecked, -Sum(PDIG.[Referral Check]) AS
ReferralCheck, -Sum(PDIG.[Billing Record Check]) AS BillingRecordChecked,
-Sum(PDIG.[A/O]) AS AOChecked, -Sum(PDIG.[Electronics]) AS ElectronicsChecked
FROM PDIG
GROUP BY PDIG.[Date Received], PDIG.[Associate Last Name], PDIG.[Associate
First Name], PDIG.[Time Spent]
HAVING (((PDIG.[Date Received]) Between #1/22/2007# And #1/26/2007#) AND
((PDIG.[Associate Last Name])="deskiewicz"));

 

[John Vinson]

I'm trying to use the below query in a report that is using several queries.
I need to add up how much time they spent doing each of the following [C/R
G/I B/GCheck], [DatabaseCheck], [NAFTA Check],[Referral Check], [Billing
Record Check], [A/O] and [Electronics] but I can't get it to total each of
them seperately.


SELECT PDIG.[Date Received], PDIG.[Associate Last Name], PDIG.[Associate
First Name], Sum(PDIG.[Time Spent]) AS [SumOfTime Spent], -Sum(PDIG.[C/R G/I
B/G Check]) AS CrGiBgChecked, -Sum(PDIG.[Database Check]) AS DatabaseChecked,
-Sum(PDIG.[NAFTA Check]) AS NAFTAChecked, -Sum(PDIG.[Referral Check]) AS
ReferralCheck, -Sum(PDIG.[Billing Record Check]) AS BillingRecordChecked,
-Sum(PDIG.[A/O]) AS AOChecked, -Sum(PDIG.[Electronics]) AS ElectronicsChecked
FROM PDIG
GROUP BY PDIG.[Date Received], PDIG.[Associate Last Name], PDIG.[Associate
First Name], PDIG.[Time Spent]
HAVING (((PDIG.[Date Received]) Between #1/22/2007# And #1/26/2007#) AND
((PDIG.[Associate Last Name])="deskiewicz"));

Well, since we have no way to know which tables contain what data, how
they are related, or just what totals you're expecting, I don't see
how anyone can be much help.

You may want to use separate Controls on the Report using DSum() to
calculate totals. It looks like you're summing Yes/No fields? Where in
your table is the "time spent" stored?

John W. Vinson[MVP]

 

[pietlinden]

I need to add up how much time they spent doing each of the following
[C/R
G/I B/GCheck], [DatabaseCheck], [NAFTA Check],[Referral Check],
[Billing
Record Check], [A/O] and [Electronics] but I can't get it to total
each of
them seperately.

<SHIVER>
sounds like your data structure is not ideal. If you had a structure
like
CREATE TABLE TaskTime(
PersonID LONG,
TaskType TEXT(20),
TimeStart DATE/TIME,
TimeFinish DATE/TIME
)

then you could just create a basic summary query. filter for the date
range you need, the types of Tasks, and then just create a totals
query. And you're done.