|
|
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])
"Paul" <muelpaul[ at ]hotmail.com> wrote in message
news:CB58261F-E971-4DD8-BE5E-80395C2F3494[ at ]microsoft.com...
[Quoted Text] > 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
|
|
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" <paul[ at ]hideme.broadwayData.com> wrote in message
news:OxJIEXKUJHA.5284[ at ]TK2MSFTNGP06.phx.gbl...
[Quoted Text] > 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" <muelpaul[ at ]hotmail.com> wrote in message
> news:CB58261F-E971-4DD8-BE5E-80395C2F3494[ at ]microsoft.com...
>> 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
>
|
|
|