Page 1 of 7 123 ... LastLast
Results 1 to 15 of 93
  1. #1
    Join Date
    Feb 2006
    Posts
    213

    Unanswered: help write sql queries

    Ok, i have 3 final sql queries that need to be written but im stuggling. can anyone offer any support?

    Here are the 3 queries.

    Query 1 - Number Of Descrite Spills

    (A discrete spill is when the spill stops and starts again later in the column.)

    ------------------------------------

    Query 2 - Total Spill Volume (Only of timesteps where spill rate is >0.001)

    -------------------------------------

    Query 3 - Peak Spill Rate (In litres per second)

  2. #2
    Join Date
    Feb 2006
    Posts
    213
    bump bump bump

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Marley

    Delighted to hear you have sorted the export.

    Query 1 is a complete nightmare.

    What have you got for query 2?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2006
    Posts
    213
    Ah sorry number 2 is done. Its this one:

    Number Of Hours Spill (depends on the output timestep)

    That isnt done.



    Number 1 is a real important one. Any ideas on the approach?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ignore 1 for now.

    The below funtion will calculate your timestep in seconds. The first time it is run it will be slow but it will be very quick ater that. You might find storing the result in a table more efficient when you use it in your queries. This could be completed in SQL but it is slow on my machine (in fact some versions crashed) so I doubt yours could cope with it. You will want to create some indexes, especially on [Time] (check out Access Help about these).

    The Universalise date is something I use to get round the America-centricism of Access (and other MS products) when it comes to dates. If your dates are mm/dd/yyyy format then remove it.

    Code:
    Public Function TimeStepInSeconds() As Integer
     
        Static TS As Integer
        
        Dim daoRst As DAO.Recordset
        Dim db As DAO.Database
        Dim FirstDate As Date
        
        If TS = 0 Then
        
            Set db = Application.CurrentDb
            
            Set daoRst = db.OpenRecordset("SELECT DISTINCT TOP 1 [Time] " & _
                                            "FROM tblImportTableTest " & _
                                            "ORDER BY [Time]", dbOpenForwardOnly, dbReadOnly)
            
            FirstDate = daoRst.Fields(0).Value
            daoRst.Close
            
            Set daoRst = db.OpenRecordset("SELECT DISTINCT TOP 1 [Time] " & _
                                            "FROM tblImportTableTest " & _
                                            "WHERE [Time] <> #" & UniversaliseDate(FirstDate) & "# " & _
                                            "ORDER BY [Time]", dbOpenForwardOnly, dbReadOnly)
            
            TS = DateDiff("S", FirstDate, daoRst.Fields(0).Value)
            daoRst.Close
            Set daoRst = Nothing
            db.Close
            Set db = Nothing
            
        End If
        
        TimeStepInSeconds = TS
            
    End Function
     
    Public Function UniversaliseDate(sqlDat As Variant) As String
     
        UniversaliseDate = (Mid([sqlDat], 7, 4) & "-" & Mid([sqlDat], 4, 2) & "-" & Mid([sqlDat], 1, 2)) & Mid(sqlDat, 11)
     
    End Function
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hey Marley -

    how about sharing some of your knowledge here - sounds exactly like the issue you have recently solved - a little bit of "putting something back"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2006
    Posts
    213
    how will i call this and have it added to the export like the other queries if its written as a function. Ive never seen this done so im asking simply to learn .

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Use as you would a column:

    Code:
    SELECT tblImportTableTest.*, TimeStepInSeconds() AS TimeStepInSeconds
    FROM tblImportTableTest
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - the function relies on your previous confirmation of this
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Feb 2006
    Posts
    213
    SELECT tblImportTableTest.*, TimeStepInSeconds() AS TimeStepInSeconds
    FROM tblImportTableTest;


    This gives an 'Undefined function error' for timestepsinseconds.

    As u reffered to in that single post.. the gap in between timesteps WILL always be the same.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have you pasted the function into the very same db you are running the SQL in?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2006
    Posts
    213
    Ha, am i really that bad? - dont answer that!

    Heres the database for your viewing pleasure.

    I pasted it to the bottom of the builder.
    Attached Files Attached Files

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No - you need to put the code in a normal code module. A form module is a special kind of Class Module (we won't get into that now) - suffice to say that any code within a form module is limited in scope to that module (can't be seen anywhere else in your project) *


    *unless you create an interface (which we won't do here )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2006
    Posts
    213
    Ah hot stuff. Well done mr guru.

    The query now runs, but its giving me a table of results instead of the one final answer. How do i get the final answer eg. 40000 (hours)

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - the SQL was a demo on just how simple it is to use the function once it is written.

    You need to start writing some SQL to get what you are looking for and then, eventually, use that function as part of your formula once you are satisied you have the correct results per timestep (remember the post where I said "you are taking our volume in one hour, converting it to seconds and then converting it back to an hour" - you will need to use that logic but this time using you TimeStepInSeconds function).
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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