Sum Question

[Guest]

I have 4 colums
Date Amount 1 Amount 2 amount 3

i would like to capture al data in a certain date range and sum the amount
sepetately for the entire date range. I get the date to list out
<#6/5/2007# but the rows print out individually w/o summing

any help would be great

 

[Guest]

here is the exsisting SQL

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud, Sum(BUDGET.OT_Hours_Bud) AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]
FROM BUDGET
GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE]
HAVING (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"));

 

[Michel Walsh]

Change the HAVING word for WHERE, and push the clause between the FROM and
the GROUP BY clauses:

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud, Sum(BUDGET.OT_Hours_Bud) AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]

FROM BUDGET

WHERE (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"))

GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE]




Sure, the sums occur for each 'group'. If you just have one record, per
'group', you won't ending by summing much.



Vanderghast, Access MVP

here is the exsisting SQL

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud, Sum(BUDGET.OT_Hours_Bud)
AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]
FROM BUDGET
GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE]
HAVING (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"));

I have 4 colums
Date Amount 1 Amount 2 amount 3

i would like to capture al data in a certain date range and sum the
amount
sepetately for the entire date range. I get the date to list out
<#6/5/2007# but the rows print out individually w/o summing

any help would be great

 

[Guest]

Week Month Week_Ending SumOfReg_Hours_Bud SumOfOT_Hours_Bud SumOfTemp_Hours_Bud DRIVER/WAREHOUSE
1 June 5/27/2007 320 80 500 W
2 June 6/3/2007 320 80 500 W

i still get the same result i am looking for the sum of (320+320), (80+80)
and (500+500)

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud, Sum(BUDGET.OT_Hours_Bud) AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]

FROM BUDGET

Where (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"))

GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE];

Change the HAVING word for WHERE, and push the clause between the FROM and
the GROUP BY clauses:

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud, Sum(BUDGET.OT_Hours_Bud) AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]

FROM BUDGET

WHERE (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"))

GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE]




Sure, the sums occur for each 'group'. If you just have one record, per
'group', you won't ending by summing much.



Vanderghast, Access MVP

here is the exsisting SQL

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud, Sum(BUDGET.OT_Hours_Bud)
AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]
FROM BUDGET
GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE]
HAVING (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"));

I have 4 colums
Date Amount 1 Amount 2 amount 3

i would like to capture al data in a certain date range and sum the
amount
sepetately for the entire date range. I get the date to list out
<#6/5/2007# but the rows print out individually w/o summing

any help would be great

 

[Michel Walsh]

Then, don't group (and don't select) the other fields:




SELECT Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud,
Sum(BUDGET.OT_Hours_Bud) AS SumOfOT_Hours_Bud,
Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud

FROM BUDGET

WHERE (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"))





Vanderghast, Access MVP

Week Month Week_Ending SumOfReg_Hours_Bud SumOfOT_Hours_Bud
SumOfTemp_Hours_Bud DRIVER/WAREHOUSE
1 June 5/27/2007 320 80 500 W
2 June 6/3/2007 320 80 500 W

i still get the same result i am looking for the sum of (320+320), (80+80)
and (500+500)

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud, Sum(BUDGET.OT_Hours_Bud)
AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]

FROM BUDGET

Where (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"))

GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE];

Change the HAVING word for WHERE, and push the clause between the FROM
and
the GROUP BY clauses:

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud, Sum(BUDGET.OT_Hours_Bud)
AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]

FROM BUDGET

WHERE (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"))

GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE]




Sure, the sums occur for each 'group'. If you just have one record, per
'group', you won't ending by summing much.



Vanderghast, Access MVP

here is the exsisting SQL

SELECT BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
Sum(BUDGET.Reg_Hours_Bud) AS SumOfReg_Hours_Bud,
Sum(BUDGET.OT_Hours_Bud)
AS
SumOfOT_Hours_Bud, Sum(BUDGET.Temp_Hours_Bud) AS SumOfTemp_Hours_Bud,
BUDGET.[DRIVER/WAREHOUSE]
FROM BUDGET
GROUP BY BUDGET.Week, BUDGET.Month, BUDGET.Week_Ending,
BUDGET.[DRIVER/WAREHOUSE]
HAVING (((BUDGET.Week_Ending)<#6/10/2007#) AND
((BUDGET.[DRIVER/WAREHOUSE])="w"));


:

I have 4 colums
Date Amount 1 Amount 2 amount 3

i would like to capture al data in a certain date range and sum the
amount
sepetately for the entire date range. I get the date to list out
<#6/5/2007# but the rows print out individually w/o summing

any help would be great