We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

R_M_Ron
R_M_Ron asked
on
Medium Priority
173 Views
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
x:\directory\file1.xls
322 Found data "ffff"
455 Found data "hhhhH"
x:\directory\file2.xls
178 Found data "ffff"
444 Found data "hhhhH"
744 Found data "yyyyyy"
x:\directory\file3.xls
111 Found data "eeeee"
etc........


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
etc........

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.


Comment
Watch Question

SharathData Engineer
CERTIFIED EXPERT

Commented:
Do you have any auto increment column in your table?

Author

Commented:
no but I could add one :)

Commented:
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

Commented:
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.
Data Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Only partially addressed the problem
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.