Copy SQL from query - paste in form

  • Thread starter Thread starter Paul
  • Start date Start date
P

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
 
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])
 
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.






Paul Shapiro said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top