Bad form to include attachments in a newgroup forum. If someone needs them
they will ask you to send them to a private location/email.
You may be ignored due to the attachments. Just letting you know, I don't
think I'm well enough qualified to give you a good answer to this
question,
I'm sure there is an easier way. Pete
"Paul" <muelpaul@[EMAIL PROTECTED]
> wrote in message
news:2C32F6E9-AC38-47DD-A408-AE06459FF0EA@[EMAIL PROTECTED]
> Hello there, I am trying to fill text boxes (see attached files) with
> totals
> and averages and would like to use SQL code within the form.
>
> My current method would be to use individual queries with for each day
> (M,T,W,TH,F), month and year but would need so many queries.
> This is what I have so far...I tried pasting these into a sub but had
> multiple errors.
>
> Office 2003 is the version.
>
> Query for total quantity per year
> SELECT DatePart("yyyy",[RecyHistory].[DateRec]) AS DateRec,
> Sum(RecyHistory.PalletCount) AS SumOfPalletCount
> FROM RecyHistory
> GROUP BY DatePart("yyyy",[RecyHistory].[DateRec])
> ORDER BY DatePart("yyyy",[RecyHistory].[DateRec]);
>
> Query for monthly totals
> SELECT Sum(RecyHistory.PalletCount) AS SumOfPalletCount,
> RecyHistory.[Plastic-Metal-Moldwood],
> Format([RecyHistory].[DateRec],"mmm")
> AS DateRec
> FROM RecyHistory
> GROUP BY RecyHistory.[Plastic-Metal-Moldwood],
> Format([RecyHistory].[DateRec],"mmm")
> ORDER BY Format([RecyHistory].[DateRec],"mmm");
>
> Query for Month Avg
> SELECT DISTINCTROW Avg([month total].SumOfPalletCount) AS [Avg Of
> SumOfPalletCount], Count(*) AS [Count Of month total]
> FROM [month total];
>
> Query for weekly totals
> SELECT Sum(RecyHistory.PalletCount) AS SumOfPalletCount,
> RecyHistory.[Plastic-Metal-Moldwood],
Format([RecyHistory].[DateRec],"ww")
> AS DateRec
> FROM RecyHistory
> GROUP BY RecyHistory.[Plastic-Metal-Moldwood],
> Format([RecyHistory].[DateRec],"ww")
> ORDER BY RecyHistory.[Plastic-Metal-Moldwood],
> Format([RecyHistory].[DateRec],"ww");
>
> Query for Week AVG
> SELECT DISTINCTROW Avg([Week total].SumOfPalletCount) AS [Avg Of
> SumOfPalletCount], Count(*) AS [Count Of week total]
> FROM [week total];
>
> Query for Daily total
> SELECT Sum(RecyHistory.PalletCount) AS SumOfPalletCount,
> RecyHistory.[Plastic-Metal-Moldwood], Format(RecyHistory.DateRec,"w") AS
> DateRec
> FROM RecyHistory
> GROUP BY RecyHistory.[Plastic-Metal-Moldwood],
> Format(RecyHistory.DateRec,"w");
>
> Query for daily AVG
> SELECT DISTINCTROW Avg([Day total].SumOfPalletCount) AS [Avg Of
> SumOfPalletCount], Count(*) AS [Count Of Day total]
> FROM [Day total];
>
> Can this be coded within the form?
>
> Thanks,
> Paul
>
>
>
>
>
>
>
|
