[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-04-27
6
Medium Priority
?
164 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.


0
Comment
Question by:R_M_Ron
  • 3
  • 2
6 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35477995
Do you have any auto increment column in your table?
0
 

Author Comment

by:R_M_Ron
ID: 35478085
no but I could add one :)
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35479640
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.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:R_M_Ron
ID: 35479770
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.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 1500 total points
ID: 35480105
You can try like this.
SELECT ID, 
       Field1, 
       (SELECT t4.Field1 
          FROM Table1 AS t4 
         WHERE t4.ID = t3.ID2) AS Field2 
  FROM (SELECT t1.ID, 
               t1.Field1, 
               (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

0
 

Author Closing Comment

by:R_M_Ron
ID: 36219741
Only partially addressed the problem
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

834 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