Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Insert a loop into a query

    Hello everyone !!

    I am a new user of microsoft access because it is the database used at my work. This database enables you to find information on flight schedules at an airport through forms that command queries. One existing form enables to retrieve all the flights for a specific date. You can also refine your search to a specific airline, a concourse or a gate.
    My objective is to obtain all the flights for a Period defined by a start date and an end date. I need exactly the same information given by the existing query but for a period. I need to introduce a loop that apply the SELECT query to every single day of the period because some flights occur only a few days per week. And I canno't introduce a loop when designing a query on Access.

    I ve spent hours on google to find some assitance but i didn't find helpful information. I think I need to use VBA to create the complex query but I absolutely don't know VBA. The idea would be to copy and paste the SQL code of the existing query and then modify it into VBA. But this is just an assumption.

    I have already worked on mysql so I have a small background on database.

    I found helpful information on this forum in the past and I thank you for your assistance.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so I'm confused on this one. No, you definitely cannot put a loop in a query. So, now that's established, I have to admit I have zero idea what you want to do.

    Without using database terminology, what is it you want your query to show you?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,925
    Here's something you can try but I'm not sure if it'll achieve what you want.

    1. Create a function in a module... (Note: you can use functions which return values as an expression in a query column to return data.)
    ie.
    Function GetInfoFunction(RID as variant) as variant <- Note: RID is the recordID (autonumber) or unique identifier in the table being passed to this function which will then return the value you populate GetInfoFunction with.
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "select * from MyTable where RecordIDField = " & RID & ""
    rs.open strSQL,currentproject.connection,adopendynamic,adl ockoptimistic
    .....
    ..... (do your calculations and other totals or just return certain data here..)
    GetInfoFunction = rs!SomeField (or GetInfoFunction = myCalculatedValueVariable) <- ie. the function then returns whatever value is in GetInfoFunction.
    rs.close
    set rs = nothing
    end function

    Then in your query in a blank column, put in an expression -ie..

    CallLoop: =GetInfoFunction([RecordIDField])

    (or try this for simplicity to see how it works - save this function in a module)
    Function GetHello() as string
    GetHello = "hello"
    end function

    and in the query expression: MyHello: =GetHello()
    You'll notice in your query for that expression column, the wording "hello" will be returned for all the records in that column.

    I put functions in queries which work very well but they can take a toll calling the function for every record (especially on large datasets!)

    Usually I'll utilize a function in a query to return a value (versus using the dlookup in a query as the function needs to do some kind of calculations upon opening the recordset). ie...GetTotalDollars: MyReturnTotDollarsFunction([RecordID]) (for example to return the total dollars for the specific RecordID value. The MyReturnTotDollarsFunction would then return the total dollars from whatever recordset the function opens and sums for that specific RecordID passed to it.) I don't use this method often as it does again, take a toll on the query (ie. calling the function for every record returned) but it's good for smaller recordsets.

    I'll especially use it with the getuser() function (ie. in the codebank) to return the current user in my append queries. (Note also: you can pass multiple "field" values to a function (to use in your SQL statement/calculations) but they can return only 1 value.)

    ex: MyExample: =GetNumDays([StartDate],[EndDate]) where the GetNumDays function performs some calculations (which can't be done using the dateadd() function in the query) on the 2 dates passed to the function and displays whatever value GetNumDays returns. (Note: StartDate and EndDate are actual fields in the table just like RecordID in the example above.)

    Hope that helps.
    Last edited by pkstormy; 06-25-09 at 01:03.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    What sort of access do you have to the database (user, designer, administrator, etc)? If you have some form of design access, you can change the enquiry form to have two date fields for defining your search period. Then you amend the query that sits under the form to that it returns all records that fall between your specified dates.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jun 2009
    Posts
    3

    Insert a loop into a query

    Quote Originally Posted by weejas
    What sort of access do you have to the database (user, designer, administrator, etc)? If you have some form of design access, you can change the enquiry form to have two date fields for defining your search period. Then you amend the query that sits under the form to that it returns all records that fall between your specified dates.
    Thank you for your answers

    I have a design access, and the original query was designed through the design interface (not by me). Weejas, could you give me some details to implement your solution ?

  6. #6
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    970
    If you post the SQL from the underlying query, I or someone else will be able to advise you on which bits to change. Don't forget to add the extra field to the enquiry form as well!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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