Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    looping through a query (2000)

    I need to build a query that takes a single record and evaluates it for about 10 different criteria before assigning it a specific category. After it is assigned to a category then I need to do the same with every subsequent record. After all that I need to total each category. Any ideas for how to do this would be great. Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: looping through a query (2000)

    Could you tell us more about the way a record is evaluated and a category is assigned? It may be possible to do this in a query, but if the process is very complicated, it might be more efficient to either write a custom VBA function for use in the query, or do it all in VBA.

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through a query (2000)

    The record being evaluated has to do with surgical data. Each record has 22 fields that are evaluated through a fairly complex algorithm to determine the category in which they belong.

    The algorithm first checks to see if the patient died, if not then whether this was re-surgery. Then it works through a number of fields evaluated in combination to determine the precise type of surgery performed.

    Does this give enough information to give an idea whether this is better suited to VBA. Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: looping through a query (2000)

    From your scanty description, I would guess that it will be necessary to use VBA. If you would like to be helped with that, you will have to provide detailed information.

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

    Re: looping through a query (2000)

    I did something similar to this once for a client, however, it involved a custom class that manipulated a couple of ADO recordsets to achieve the desired result. This definitely requires advanced VBA coding, not a simple query or queries.
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: looping through a query (2000)

    I agree with Hans - this type of thing is usually beyond the logical capabilities of SQL. Access does allow the use of IIF() in queries, so some logical things can be done, but there isn't any real branching and looping capability in SQL. What you will most likely need to do is open either a DAO or an ADO recordset in VBA and process each record to calculate the category the record should belong in, and then update the record. You may want to pick up a book that goes into detail on using VBA for this kind of thing - my favorite is "Beginning Access 2000 VBA" by Smith and Sussman. You should be able to find it on Amazon, or since you are from Colorado, you might want to look at Softpro Books in the Denver Tech Center.
    Wendell

  7. #7
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through a query (2000)

    This report is completed as a stored procedure on the department's SQL Server. The setup is that there will be a local desktop version that uses Access. The data is then sent to the SQL Server. Is it possible to use the stored procedure locally within Access? I have attached the stored procedure.

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: looping through a query (2000)

    Thanks Hans, I appreciate you looking at this. I have requested a document that provides more information on this process and will post it when I have it.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,613
    Thanks
    3
    Thanked 58 Times in 58 Posts

    Re: looping through a query (2000)

    Based on a quick look at the stored procedure, it appears to be creating a temporary table in SQL Server for use by some unknown process. I'm not sure how that relates to the local Access desktop version. Access can attach to SQL Server tables and perform all sorts of functions - we routinely use Access as a front-end to SQL Server databases. There is another option using what is known as an ADP, that works with SQL Server tables in a more or less native mode and can employ SQL Server stored procedures. In addition, you can use what is known as a "pass-through" query that can call or execute stored procedures from an Access MDB database. This sort of stuff is fairly advanced development that most Access users don't get into, but if you want to explore it in more depth, I can share our experiences over the last 12 years.
    Wendell

Posting Permissions

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