Results 1 to 12 of 12
  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 create a Report in the same file that should display the average of the value of a calculated field(which is = Energy/24) for each same day throughout the four years and display in a textbox the number of times that average gets below the value typed in a textbox on a form in the same file. For example, I take the values of Energy/24 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 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.
    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, tblMonthOrder.OrderOfMonth ORDER BY tblMonthOrder.OrderOfMonth"
    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?
    Note: I wanted to post the relevant mdb file too but its size is 170k 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
    Star Lounger
    Join Date
    Apr 2002
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Averaging same-day records in a table

    If I understand your request correctly I think you're working too hard.

    Try pasting the following SQL statement in a new query. It should get you to your 365 averages. Then add your criteria statement from the form where the user enters the value.

    Finally, base your report on the finished query.


    SELECT Format([Anno],"mm/dd") AS MMDD, Avg([Energy]/24) AS AvgEnergy
    FROM Mediegio
    GROUP BY Format([Anno],"mm/dd");


    Best of luck.

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

    Praise your effort...

    But the query based on your SQL statement gives me only 4 records, 1 per year.
    Would you like to receive the mdb file?

  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: Praise your effort...

    Hi,
    Having looked at your database, I'd suggest creating a new blank database and importing all your objects into it. If you look in MSysQueries table you will see that qryProdGIO exists and is trying to sort on Anno even though it's not retrieving Anno as part of the query. I tried looping through all the views in the catalog and deleting them but it ignores qryProdGIO, behaving as though it doesn't exist. However it will not allow me to append a new qryProdGIO saying it already exists and it won't allow me to change the Command for it either! I don't know how this happened but a Compact and Repair doesn't help (I didn't try Jetcomp so that might be worth a go) so I can only suggest starting again I'm afraid.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Praise your effort...

    Rory,

    I can answer the question about the query. In Access 2000, there is no Views collection in an mdb. So if you create a query using ADO and append it to the Views collection, it becomes a hidden query and can only be accessed from code, since there is no public interface. I use that technique to create queries that will only be called from code and where the user will have access to the database window. Keeps them from deleting a critical query by mistake. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    You should be able to delete it from code using something like this:

    <pre>Sub DeleteView(strViewName As String)
    Dim catDB As ADOX.Catalog
    Set catDB = New ADOX.Catalog
    ' Open the catalog.
    catDB.ActiveConnection = CurrentProject.Connection

    catDB.Views.Delete strViewName
    Set catDB = Nothing
    End Sub 'DeleteView(strViewName As String)</pre>


    If that doesn't work (air code, untested), try looping through the Views collection until you find the right name and then using it's ordinal to delete it.
    Charlotte

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

    Corruption is the bane of mankind.

    Whoa! I had to look into the Microsoft Jet Database Engine Programmer's Guide to see what you meant by MSysQueries table. But it doesn't go further than that.
    How can you look into MSysQueries and see that qryProdGIO is trying to sort on Anno?

  7. #7
    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: Praise your effort...

    Charlotte,
    The thing that is confusing me in this case is that if I loop through Cat.Views, I get 2 views - these are both normal queries visible in the query window. However if I try and append a View called qryProdGIO, which is not the name of either of the other queries, it tells me this already exists. If I look in MSysObjects or MSysQueries I can see that there are 3 queries there, one of which is indeed qryProdGIO but I can't access or delete it even in code! Any idea how that would arise?
    PS I've just tried something so blindingly obvious that I missed it before, which was to create a new query and save it as qryProdGIO - Access asked me if I wanted to replace the existing query and all is now fine! Sometimes I can't see the wood for the trees. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    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: Corruption is the bane of mankind.

    If you go into Tools-Options on the View tab, there's a checkbox for System Objects. If you check this, several new tables will appear in the database window. I wouldn't normally recommend it, but your problem was confusing the hell out of me!
    I've actually just discovered that you can remove the offending query by creating a new query in design view (any query will do) and saving it as qryProdGIO. You can then delete that query and append a new View in code.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Praise your effort...

    I've taken a gander at the MSysQueries table for the Finalborgo.mdb I sent you because you say, "you will see that qryProdGIO exists and is trying to sort on Anno". Well, I don't see qryProdGIO mentioned anywhere in the table and would like to know what detail in the table makes you say that is trying to sort on Anno.
    I've also launched the Database compact utility 4.0 downloaded from http://www.conxion.com/download/access2000...-us/jetcu40.exe>http://msvaus.<A target="_blank" HREF=http://www.conxion.com/download/access2000/utility/1.0/win98me/en-us/jetcu40.exe>http://www.conxion.com/download/access2000...-us/jetcu40.exe</A></A> but it gives the message: Error compacting database

  10. #10
    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: Praise your effort...

    Sorry, I was slightly misleading! To see the name qryProdGIO you'll have to look in MSysObjects. For that record you'll see a ParentID. That is what appears in the MSysQueries table. You can see that it's trying to sort on Anno because Anno appears in the Expression column with attribute 11 (Sort) as well as attribute 8 (I think it was 8, I don't have the db here)
    I'd try what I suggested before and see if that fixes it. Your SQL should then work OK.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Praise your effort...

    The ParentID number corresponding to qryProdGio(251658241) found in MSysObjects isn't present in MSysQueries.
    How do you know that attributes 8 and 11 in the Expression column correspond to sort?

  12. #12
    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: Praise your effort...

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>I have no idea where my brain was yesterday! I should have said ID rather than ParentID. The attribute 8 indicates a WHERE clause, attribute 11 an ORDER BY. You can figure them out by creating a query normally and then looking at the entries in the MSysQueries table for that query.
    Apologies for all the confusion - that's what happens when I try and do too many things at once. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> <img src=/S/bash.gif border=0 alt=bash width=35 height=39> <img src=/S/toilet.gif border=0 alt=toilet width=24 height=26>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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