MS Access Query to return data from different records based on if statement conditions

Posted on 2011-04-27
Last Modified: 2012-05-11
Not sure if there is an efficient way to accomplish this without excel or other external tools.  I am trying to take a table of data that looks similar to te following (there are over 1 million records in my table):

Field 1
322 Found data "ffff"
455 Found data "hhhhH"
178 Found data "ffff"
444 Found data "hhhhH"
744 Found data "yyyyyy"
111 Found data "eeeee"

I want to run a query that will give me a result as follows (based on example above):

Field 1                                                  Field2
322 Found data "ffff"                            x:\directory\file1.xls
455 Found data "hhhhH"                      x:\directory\file1.xls
178 Found data "ffff"                            x:\directory\file2.xls
444 Found data "hhhhH"                      x:\directory\file2.xls
744 Found data "yyyyyy"                      x:\directory\file2.xls
111 Found data "eeeee"                      x:\directory\file3.xls

Any ideas on how to do this?  In excel I would do it with IF conditions looking to the rows above but in access I have no clue.

Question by:R_M_Ron
    LVL 40

    Expert Comment

    Do you have any auto increment column in your table?

    Author Comment

    no but I could add one :)
    LVL 44

    Expert Comment

    Add it pls.  To create the recordset you want from the table you show in the question will require some code and a way to refer to a precise location in the table.

    Author Comment

    So no good built in functions?  I am afraid to use a UDF that needs to process against 1M + records (guessing this will kill my PC)  I will give it a try though and post back.  Thanks for the lead on this.
    LVL 40

    Accepted Solution

    You can try like this.
           (SELECT t4.Field1 
              FROM Table1 AS t4 
             WHERE t4.ID = t3.ID2) AS Field2 
      FROM (SELECT t1.ID, 
                   (SELECT MAX(t2.ID) 
                      FROM Table1 AS t2 
                     WHERE t2.Field1 LIKE "*xls" 
                           AND t2.ID < t1.ID) AS ID2 
              FROM Table1 AS t1) AS t3 
     WHERE t3.Field1 NOT LIKE "*xls";

    Open in new window


    Author Closing Comment

    Only partially addressed the problem

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now