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
"Paul" <muelpaul[ at ]hotmail.com> wrote in message
news:76AEB8EA-B65E-45D8-821C-AA24C0173FDB[ at ]microsoft.com...
[Quoted Text] > 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
|