Query problem - used in form

[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

 

[Jeff Boyce]

Paul

If you want to use daily totals in computing your average, first you need to
get daily totals. Take a look at the Totals tool in the query design view.
After building a query that Totals by [DateRec], build a second query
against that first one that does your "averaging".

Regards

Jeff Boyce
Microsoft Office/Access MVP