Can a query find info X numbr of rows from target info?

[Guest]

Hi. I am running Access 2003 under Windows XP. I have a crazy looking
output file that I need to analyze. The system we are working with creates
output files that can run 3,000 - 35,000 rows long by two columns wide. Of
these rows, maybe a few hundred are relevant to our needs.

Column 1 is basically throwaway. One of the things I need to do with the
column 2 data is find information that is 1, 4 or 5 rows below a target
phrase. For example, if row 156 contains the word DUMPEND, I need to
identify the the words that appear in rows 157, 160 and 161.

I know that in Excel I could concatenate the words based on cell references.
Is there a way in Access to concatenate data based on the records' relation
to one another in the same table?

Thanks for any help you can give me.

Ann Scharpf

 

[Guest]

Excel has Row and Column names, with an explicit sort order.

If you want to do something like that in Access, you need
a numeric Row index I see that you have Column (Field)
namesL: Do you have Row names (Primary Key). If you
do have a Primary Key, does it have an explicit sort order?


Start in the query design view by adding the table, selecting
the 'dumpend' records.

Then add the same table again, and join on the index field.

Then go into SQL view, and change the join from (n=n) to
(n = n-1).

select * from tbl inner join tbl as tbl1 on (tbl.pk = tbl1.pk-1)
where tbl.fld = 'dumpend'


Test as see that it is working. When happy, START AGAIN
(you can't go back to design view) with 4 joined copies of
the table, and change all 4 joins in sql view.

(david)

 

[Guest]

Hi, David:

Thanks for your response. (I have been away for the Thanksgiving holiday
and just checked back in.)

Yes, when I import the data file, I am letting Access create an index. So
each row is sequentially numbered, giving me the desired explicit sort order.

I can see where you're going with the join (n=n-1) but I don't have that
type of statement in my SQL. I was getting a little lost translating between
my names and your example, so I renamed my data "tbl" & "fld" so I could make
an exact comparison between my SQL & your advice. This is what I'm getting:

SELECT tbl.fld
FROM tbl INNER JOIN tbl AS tbl_1 ON tbl.ID = tbl_1.ID
WHERE (((tbl.fld) Like "*dumpend*"));

I only have the one field in the table (aside from the index), so do I need
to do this part?

select * from tbl inner join tbl as tbl1 on (tbl.pk = tbl1.pk-1)
where tbl.fld = 'dumpend'

I didn't understand the "pk" part, as I don't see this in my SQL.

Thanks very much for taking the time to help me.

Ann

 

[Guest]

Well, duh! PK is primary key! The (n=n-1) is just explaining what the
select statement is doing.

I just had to step away from it for a minute and then it made sense.

Thanks. I will try again now that I understand what you're saying.

Ann

 

[Guest]

This is beautiful - BRILLIANT! It does EXACTLY what I need! This query is
infinitely better than the monster file I would have had to create in Excel.
Thank you so much, David!

 

[Guest]

Thank you. It is always encouraging to see something working :~)

(david)

This is beautiful - BRILLIANT! It does EXACTLY what I need! This query is
infinitely better than the monster file I would have had to create in Excel.
Thank you so much, David!

..