P
Paul
Hello there, I am having trouble getting correct output from the query
listed below and I would really appreciate help.
'Sample - RecyHistory Table Data:
DateRec PalletCount
12/1/08 2
12/1/08 3
12/8/08 5
12/15/08 2
12/15/08 6
'Query to get average number of pallets received for each day of the week -
in this case just Monday from the sample table.
SELECT DatePart("w",RecyHistory.DateRec) AS DayOfTheWeek,
Sum(RecyHistory.PalletCount) AS PalletCountTotal,
Avg(RecyHistory.PalletCount) AS PalletCountAverage
FROM RecyHistory
GROUP BY DatePart("w",RecyHistory.DateRec);
' Output of the above Query: "uses every date (5 total) to use for average"
DayOfWeek PalletCountTotal PalletCountAverage
2 18 3.6
' What I am looking for:
DayOfWeek PalletCountTotal PalletCountAverage
2 18 6
' Dates should be "unique", and the pallet Count should be summed
accordingly, ie:
12/1/08 3 + 2 =5
12/8/08 =5
12/15/08 2 + 6 =8
I would like to place this in the form open sub and have it's output sent to
textboxes, but first I would like to get the correct output.
Thanks
Paul
listed below and I would really appreciate help.
'Sample - RecyHistory Table Data:
DateRec PalletCount
12/1/08 2
12/1/08 3
12/8/08 5
12/15/08 2
12/15/08 6
'Query to get average number of pallets received for each day of the week -
in this case just Monday from the sample table.
SELECT DatePart("w",RecyHistory.DateRec) AS DayOfTheWeek,
Sum(RecyHistory.PalletCount) AS PalletCountTotal,
Avg(RecyHistory.PalletCount) AS PalletCountAverage
FROM RecyHistory
GROUP BY DatePart("w",RecyHistory.DateRec);
' Output of the above Query: "uses every date (5 total) to use for average"
DayOfWeek PalletCountTotal PalletCountAverage
2 18 3.6
' What I am looking for:
DayOfWeek PalletCountTotal PalletCountAverage
2 18 6
' Dates should be "unique", and the pallet Count should be summed
accordingly, ie:
12/1/08 3 + 2 =5
12/8/08 =5
12/15/08 2 + 6 =8
I would like to place this in the form open sub and have it's output sent to
textboxes, but first I would like to get the correct output.
Thanks
Paul