Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Feb 2013
    Posts
    21

    Unanswered: Average data over last 10 Depths?

    Hello all,

    Hopefully this is possible, but I'm not sure. I have a table similar to the one attached (though much larger). I am interested in querying the data so that I can average the data for temperature, salinity and beam transmission over the last 10 depths for each CTD_ID.

    For each CTD_ID the depth ranges differ so there is no set depth for me to query, i.e. 'between 30 and 39'. Is there a way to query 'last -10' or something of the like, for the depth field?

    I have tried numerous combinations with no luck. Any suggestions are very appreciated!

    cheers,
    Jeanette
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nevermind! I was in the wrong forum.

    Yes, see the Lag() function and the OVER clause for details.

    -PatP
    Last edited by Pat Phelan; 02-26-13 at 19:05.
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2013
    Posts
    21
    Thank you Pat,
    I don't know much about SQL but it's nice to have a direction and some leads! I'll look into it.

    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry Jeanette, but I posted that response before I realized that you are using MS-Access. Those are pretty advanced features, and they aren't available unless you connect to a pretty full featured modern database. The MS-Access default database engine doesn't provide those features.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    How do you define last in "last 10 depths"?
    Have a nice day!

  6. #6
    Join Date
    Feb 2013
    Posts
    21
    Thanks for the clarification Pat, I've been looking through all kinds of SQL fundamentals books and online to try and figure that one out! LOL..

    Hi Sinndho,
    the 'last' 10 depths is meant to mean the 10 deepest depths for each CTD_ID. Hope that helps!

    thanks,
    Jeanette

  7. #7
    Join Date
    Feb 2013
    Posts
    21
    So, unless someone knows how to trick access into doing some sort of 'last -10' type of criteria (though I've already tried that specific one plus others)....

    I'm guessing this is just not possible for MS Access and that I'll need to use matlab or some other 'full featured' database as Pat suggested.

    Is this correct?

    Thanks for your help Pat and Sinndho!

    Jeanette

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This discussion is getting into topics that aren't difficult, but are rarely addressed by newcomers to MS-Access. None of this is hard, but it probably won't be covered in any of the introductory texts or blogs...

    You definitely CAN write the analytic functions you are describing using the tools provided with MS-Access. That will mean learning some VBA, and doing some fairly extensive customization but it would be pure MS-Access which makes it cheap and easy to distribute.

    There is another answer that requires about $50 USD, but is more appealing to me for several reasons.

    MS-Access has evolved greatly over the span of almost two decades. It has grown organically, picking up bits of flotsam and jetsam as it went along.

    The core product was once a simple GUI that was cobbled onto the Jet engine. It later acquired a nice reporting engine, some great analytic tools, a kitchen sink, pontoons, and a lawnmower. The resulting package has become pretty much the default database used by office workers and other professionals today.

    The problem is that MS-Access has evolved, and has seen MANY different incarnations. We refer to it as though it were one product, but in fact it is more like several dozen.

    One of its best features is that it is "database engine agnostic" to a very large degree. As long as there is an ODBC or an OLE-DB driver for a database engine, MS-Access can probably use it. This means that you don't have to give up MS-Access cold turkey and often you don't have to give up much of it in order to get whatever features you need.

    Some versions of MS-Access come with Microsoft SQL Server or one of its close cousins (like MSDE) already under the hood. For around $50 USD you can add SQL Server Developer Edition which will bring you the full syntactic power of SQL Server (and a practical infinity of data capacity), along with several other data management/analysis tools that you don't need today but might make you drool as you figure them out!

    In short, you can keep the tool that you know (MS-Access) and replace only the data engine to get the features that you want (along with a treasure trove of other toys to be explored at your leisure). Based on your postings, I don't think that the cost will deter you and the convenience of being able to continue using the tools you already know is probably a huge plus too.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Not a very elegant solution, and surely not a "pure SQL" one, however the fastest way (I tested several possible solutions) consists in creating a secondary table to store the 10 rows retained for each Id:
    Code:
    Sub ExtractLastTenDepths()
    
        Const C_SQL As String = "INSERT INTO Tbl_LastTenDepths ([CTD ID], Depth, Salinity, [Primary Temperature], [Beam Transmisison]) " & _
                                "SELECT TOP 10 [CTD ID], Depth, Salinity, [Primary Temperature], [Beam Transmisison] " & _
                                "FROM Exampletbl " & _
                                "WHERE Exampletbl.[CTD ID] = @ " & _
                                "ORDER BY Exampletbl.Depth DESC;"
    
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        CurrentDb.Execute "DELETE FROM Tbl_LastTenDepths;"
        strSQL = "SELECT DISTINCT [CTD ID] As Id FROM Exampletbl;"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        With rst
            Do Until .EOF
                CurrentDb.Execute Replace(C_SQL, "@", !Id), dbFailOnError
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        
    End Sub
    Form there, the query to get the results is very straightforward:
    Code:
    SELECT Tbl_LastTenDepths.[CTD ID], 
           Avg(Tbl_LastTenDepths.Salinity) AS AvgSalinity, 
           Avg(Tbl_LastTenDepths.[Primary Temperature]) AS AvgPrimaryTemperature, 
           Avg(Tbl_LastTenDepths.[Beam Transmisison]) AS AvgBeamTransmisison
    FROM Tbl_LastTenDepths
    GROUP BY Tbl_LastTenDepths.[CTD ID];
    Have a nice day!

  10. #10
    Join Date
    Feb 2013
    Posts
    21
    Wow, thank you all for your help!!

    Pat,
    Thank you so much for taking the time to help! Unfortunately at my job I do not have any sway over IT expenses and I'm unfortunately an inexperienced database person as well. My background is science but I got put in charge of our access database (eventually will be going into an oracle database with a GIS front end), so your second option while tempting is not feasible for me.

    Sinndho, thank you!!
    Any chance you could 'decode' the code for me? I've tried copy/pasting the code into the SQL view of my query but it's giving me all sorts of error messages. I apologize I'm not very adept with this stuff (though I'm eager to learn more if I can!)

    I really appreciate both of your help and thank you for taking the time to respond!

    cheers,
    Jeanette

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. The first step consists in creating a (new) empty table with the same structure as the table containing the data to be analyzed (Exampletbl in the sample data you posted). You can :
    - copy/paste the original table and select the option "Structure Only" when asked for the (new) table name (depending on the version of Access, the exact message can vary),
    or:
    - use the following Make-Table query:
    Code:
    SELECT [CTD ID], Depth, Salinity, [Primary Temperature], [Beam Transmisison] 
    INTO Tbl_LastTenDepths
    FROM Exampletbl
    WHERE Exampletbl.[CTD ID]=0;
    or:
    - Use a DDL query to create the table:
    Code:
    CREATE TABLE Tbl_LastTenDepths ( [CTD ID] LONG, Depth SINGLE, Salinity SINGLE, [Primary Temperature] SINGLE, [Beam Transmisison] SINGLE );
    In any case, the important points are that:
    - the name of the table must be Tbl_LastTenDepths
    - the table Tbl_LastTenDepths must have 5 columns named [CTD ID], Depth, Salinity, [Primary Temperature], [Beam Transmission]
    - the data type of each column must be the same as the one of each matching column in the original table.

    2. The sub procedure ExtractLastTenDepths() must be copied/pasted in a new or existing Module (you can also re-type it if you really want to).

    3. The SQL expression I provided must go in a new query.

    When you want to compute the average values:
    - run the procedure ExtractLastTenDepths to fill the secondary table Tbl_LastTenDepths
    - Open the query created at step 3 here above or use it in any way you want.
    Have a nice day!

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sinndho's solution actually IS an implementation of the first solution that I was describing. It is available now using the tools that you already have, so that makes it pretty tempting "as is" for your problem!

    If this project will eventually move to Oracle, the features (windowing functions like Lag() and it's kin) are present in Oracle 10g and later. If you can switch to Oracle now, you ought to be able to use those features.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Feb 2013
    Posts
    21
    Thanks again very much to both of you!
    Jeanette
    Last edited by JeanetteG; 03-05-13 at 19:33.

  14. #14
    Join Date
    Feb 2013
    Posts
    21
    Disregard below statement, I did not initially run the module... oops!

    "ORDER BY Exampletbl.Depth DESC;"

    I'm wondering if the above statement is where the problem lies. It looks like it may order the whole table by depth, descending, then choose the top ten to average, is that correct?

    Is there a way to make it order by CTD_ID, and then depth?
    Last edited by JeanetteG; 03-05-13 at 19:32. Reason: did not run module...

  15. #15
    Join Date
    Feb 2013
    Posts
    21
    OK, sorry to keep coming back at this, but I apparently did not initially read the full instructions and so I did not actually 'run' the module initially and the query ended up averaging the data over all of the depths for each CTD ID.

    So, I went to run the module and I'm getting an error that reads "Syntax error (missing operator) in query expression '1CTD_Data.[CTD ID] = 199940591'.

    The example table was only a partial table of what I am working with so I changed all of the 'Exampletbl' names in the module to '1CTD_Data'.

    All of the structure and data types are the same for each table, but the 1CTD_Data table is much larger..

    Any ideas?

Posting Permissions

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