Help with Query 1

[egstatus]

Hi all,
I need help with the following query.

SELECT KeyerID, KeyerName, Sum(RecsKeyed) AS SumOfRecsKeyed, Sum(MoneyMade) AS SumOfMoneyMade
FROM SignOutSheet
GROUP BY KeyerID, KeyerName, DateReturned
HAVING (((KeyerID)=[Enter KeyerID:]) AND ((DateReturned) Between [Enter the Staring Date MM/DD/YYYY:] And [Enter the Ending Date MM/DD/YYYY:]));

This is what I get when I run the query:
Keyer ID Keyer Name SumOfRecsKeyed SumOfMoneyMade
H084 CHUNNEI, LEE 497 14.90
H084 CHUNNEI, LEE 849 25.47
H084 CHUNNEI, LEE 735 22.04

I only want to see the KeyID/KeyName once, not tree times like my query is returning.
I want to see:
H084 CHUNNEI, LEE 2,081 62.43

What Am I doing wrong here.

Thanks in advance

EG

ps: These are the records for that employee for the period I am working with

Keyer ID Keyer Name Date Returned RecsKeyed MoneyMade
H084 CHUNNEI, LEE 11/1/2010 92 2.76
H084 CHUNNEI, LEE 11/1/2010 405 12.15
H084 CHUNNEI, LEE 11/4/2010 231 6.93
H084 CHUNNEI, LEE 11/4/2010 618 18.54
H084 CHUNNEI, LEE 11/10/2010 184 5.52
H084 CHUNNEI, LEE 11/10/2010 546 16.38
H084 CHUNNEI, LEE 11/10/2010 5 0.15

 

[SkipVought]

hi,

try this...

SELECT KeyerID, KeyerName, Sum(RecsKeyed) AS SumOfRecsKeyed, Sum(MoneyMade) AS SumOfMoneyMade
FROM SignOutSheet

WHERE (((KeyerID)=[Enter KeyerID:]) AND ((DateReturned) Between [Enter the Staring Date MM/DD/YYYY:] And [Enter the Ending Date MM/DD/YYYY:]));

GROUP BY KeyerID, KeyerName

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[egstatus]

Thanks,

That did it.

 

[egstatus]

One more question on this.

Since I am processing records for 2-weeks period, how would I change the query to give weekly totals?

For example:
Show two records:
One that shows totals for Week 10/30/2010 to 11/5/2010, and the second record from 11/6/2010 to 11/12/2010.

I am sure this is possible, I just can't figure it out.

Thanks

EG

 

[alvechurchdata]

The Format function will return a week number if you use the ww code. This comes back as a string so you'll need to use Val to get its numeric value but you can add the week number as an additional field and then group by this new field.



Geoff Franklin

http://www.alvechurchdata.co.uk

 

[egstatus]

Yes, that helped. thanks for the hint.
EG