Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Averaging same-day records in a table

    I have a table(Mediegio) composed of daily records spanning 4 years. Each record is composed of 5 fields: Id, Anno, Mese, Giorno, Energy.
    I'd like to add a textbox to a Report footer in the same file which should display how many times the average of the value of the Energy field(which is numeric) for each day gets below the value typed in a textbox on a form in the same file. For example, I take the values of Energy for the same day each year and make an average, so basically I should obtain a 365-values recordset to be compared to the value in the form's textbox.
    I've prepared one query for each year whose code is the same to one another's except for the where-clause year:
    SELECT MEDIEGIO.Id, MEDIEGIO.Anno, MEDIEGIO.Mese, MEDIEGIO.Giorno, MEDIEGIO.Energy
    FROM MEDIEGIO
    WHERE MEDIEGIO.Anno=1997
    So now I have 4 subqueries. I should now create a query being fed by the four subqueries and this query should include a field which calculates the average of the Energy field of each of the subqueries but putting avg(query1.Energy, query2.Energy, query3.Energy, query4.Energy) as expression for the field gives me an error message.
    How can I obtain the average of same-day-record fields?
    Note: I wanted to post the relevant mdb file too but its size is 146k so if anyone cares to receive it, by all means drop a line <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Averaging same-day records in a table

    Hi,
    If I understand you correctly, to get the average Energy for each day you only need one query. It will contain Month, Day and Energy and group on month and day and apply average to the Energy - i.e. you would end up with something like:
    SELECT MEDIEGIO.Mese, MEDIEGIO.Giorno, Avg(MEDIEGIO.Cassiglio) AS AvgOfCassiglio
    FROM MEDIEGIO
    GROUP BY MEDIEGIO.Mese, MEDIEGIO.Giorno;
    the above being based on the db you sent me (not sure if Cassiglio is the correct field?) You would then need to do a running sum to get the total where AvgOfCassiglio is less than the entered value.
    Hope that helps.
    PS You will end up with 366 records due to leap years.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    He's back! With another slice of his wisdom...

    I'm glad you remembered my Post#: 39226
    so now it goes like this:
    Private Sub Report_Activate()
    Dim Media As Double, a, b, c, d, e, strSQL As String
    Set cnn = CurrentProject.Connection
    Dim cmd As New ADODB.Command
    cat.ActiveConnection = CurrentProject.Connection
    If Not Isloaded("Mask1") Then MsgBox "Input Form not loaded"
    cmd.CommandText = "SELECT Id, anno, mese, giorno, " & Forms!mask1!List11 & " as selectedfield, selectedfield/24 as powerday FROM mediegio"
    With cat.Views
    '.Append "qryProdGIO", cmd
    .Item("qryProdGIO").Command = cmd
    End With
    Set cmd = Nothing
    Reports!prodgio!Text33 = "Produzioni Giornaliere in ingresso"
    Reports!prodgio!Text34 = "Produzione Giornaliera di " & Forms!mask1!List11
    rst.Open ("qryProdGio"), cnn, adOpenKeyset, adLockOptimistic
    rst.MoveLast
    rst.MoveFirst
    <font color=magenta>rst.Filter = "selectedfield = Null"
    Text41 = "I giorni di fermo sono stati " & rst.RecordCount</font color=magenta>
    rst.Close
    Set rst = Nothing
    rst.Open "select Max(mediegio." & Forms!mask1!List11 & ")as maxgio from mediegio", cnn, adOpenKeyset, adLockOptimistic
    a = rst!maxgio
    b = a / 24000
    strSQL = "SELECT Anno,Mese,giorno FROM mediegio WHERE mediegio." & Forms!mask1!List11 & " = " & rst!maxgio
    Set rst = cnn.Execute(CommandText:=strSQL, Options:=adCmdText)
    Text43 = "il massimo pari a " & a & "(" & b & " MW) si

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: He's back! With another slice of his wisdom...

    Hi,
    It may be a simple question of there not being a space before the multiplication sign. Try changing the code to:
    rst.Filter = "AvgOfPowerday < " & Forms!Mask1!Text23 * 1000
    so that it evaluates the MW first and see if that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cut From The Same Cloth as before.

    That's what your answer is and I like that velvety <img src=/S/rose.gif border=0 alt=rose width=15 height=15> feel to it.
    I think it's better for me, beside displaying how many times AvgOfPowerday gets below the value typed in text23, to show in a report the 366 values assumed by AvgOfPowerday. I've joined Mediegio with another table( tblMonthOrder) which has two fields: Month(text) and OrderofMonth(numeric) and 12 records, 1 for each month, to have the report show the months in chronological order.
    So here's the Sub Report_Activate for the report:
    Private Sub Report_Activate()
    Set cnn = CurrentProject.Connection
    Dim cmd As New ADODB.Command
    cat.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "SELECT MEDIEGIO.Mese, MEDIEGIO.Giorno, Avg(Nz(MEDIEGIO." & Forms!mask1!List11 & "/24)) AS AvgOfPowerday FROM MEDIEGIO INNER JOIN tblMonthOrder ON MEDIEGIO.Mese = tblMonthOrder.Month where mediegio.anno between " & Forms!mask1!inizi & " and " & Forms!mask1!fin & " GROUP BY MEDIEGIO.Mese, MEDIEGIO.Giorno ORDER BY tblMonthOrder.OrderOfMonth, MEDIEGIO.Giorno"
    With cat.Views
    .Item("qryProdGIO").Command = cmd
    End With
    Set cmd = Nothing
    End Sub
    But the code stops with the following message:"You tried to execute a query that doesn't include the specified expression 'Mediegio.Anno' as part of an aggregate function." What's wrong with the Select statement?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •