Copy SQL from query - paste in form

[Paul]

Hello there,
I have two query's; one to get totals for pallets received on Mondays
and the other to get the average from the the first query...

Query one - Week Day Totals - SQL VIEW

SELECT RecyHistory.DateRec, Sum(RecyHistory.PalletCount) AS SumOfPalletCount
FROM RecyHistory
GROUP BY RecyHistory.DateRec
HAVING (((DatePart("w",[RecyHistory].[DateRec]))=DatePart("w",2)));



Query 2 - Averages - SQL VIEW

SELECT DISTINCTROW Avg([Day total].SumOfPalletCount) AS [Avg Of
SumOfPalletCount], Count(*) AS [Count Of qrytest]
FROM [Day total];



I think there must be an easier solution with code to get the pallet count
for every Monday ( and T,W,TH,F as well) and the average
pallet count ( for each weekday) and place that code within the form and
eliminate the query's altogether, but what would be the easiest way?

I have a form with text boxes for each week day - total and average.

This is my attempt so far...but I get an error with the last line.

Week Day DAO test......

Private Sub DateRec_GotFocus()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT RecyHistory.DateRec, Sum(RecyHistory.PalletCount) AS
SumOfPalletCount" _
& "FROM RecyHistory GROUP BY RecyHistory.DateRec" _
& "HAVING #" & DatePart("w", RecyHistory.DateRec) = DatePart("w", 2) & "# ;"

End Sub


Any advise would be greatly appreciated.

Thank you,
Paul

 

[Paul Shapiro]

If you want totals by day of the week, then Group By day of the week.
Something like:
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])

 

[Paul]

Thank you Paul, sorry it took so long but I only have MS Access at work.

I tried it out and thought at first it worked:

DayOfTheWeek PalletCountTotal PalletCountAverage
2 227
2.73493975903614 { actual avg should be 6.1 - with 37 Monday
shipments}
3 329
2.76470588235294 { 7.8 - 42 Tuesday shipments}
4 314
2.59504132231405 { 6.7 - 47 Wednesday shipments}
5 349
3.17272727272727 { 8.3 - 42 Thursday shipments}
6 344
3.15596330275229 { 8.2 - 42 Friday shipments}

But the averages did not come out correctly, The PalletCountTotal counts are
correct though.

I tried to play around with it today but could not figure it out.

If you want totals by day of the week, then Group By day of the week.
Something like:
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])

Hello there,
I have two query's; one to get totals for pallets received on Mondays
and the other to get the average from the the first query...

Query one - Week Day Totals - SQL VIEW
SELECT RecyHistory.DateRec, Sum(RecyHistory.PalletCount) AS
SumOfPalletCount
FROM RecyHistory
GROUP BY RecyHistory.DateRec
HAVING (((DatePart("w",[RecyHistory].[DateRec]))=DatePart("w",2)));

Query 2 - Averages - SQL VIEW
SELECT DISTINCTROW Avg([Day total].SumOfPalletCount) AS [Avg Of
SumOfPalletCount], Count(*) AS [Count Of qrytest]
FROM [Day total];

I think there must be an easier solution with code to get the pallet
count for every Monday ( and T,W,TH,F as well) and the average
pallet count ( for each weekday) and place that code within the form and
eliminate the query's altogether, but what would be the easiest way?

I have a form with text boxes for each week day - total and average.

This is my attempt so far...but I get an error with the last line.

Week Day DAO test......

Private Sub DateRec_GotFocus()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strsql As String

Set dbs = CurrentDb
strsql = "SELECT RecyHistory.DateRec, Sum(RecyHistory.PalletCount) AS
SumOfPalletCount" _
& "FROM RecyHistory GROUP BY RecyHistory.DateRec" _
& "HAVING #" & DatePart("w", RecyHistory.DateRec) = DatePart("w", 2) & "#
;"

End Sub