Re: Can a query find info X numbr of rows from target info?
- From: "Ann Scharpf" <AnnScharpf@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 29 Nov 2005 06:16:03 -0800
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
"david@epsomdotcomdotau" wrote:
> 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)
>
>
>
> "Ann Scharpf" <AnnScharpf@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:FC2D4249-5AE7-4818-94BC-4F7D9FC26F08@xxxxxxxxxxxxxxxx
> > 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
>
>
>
.
- Follow-Ups:
- Re: Can a query find info X numbr of rows from target info?
- From: Ann Scharpf
- Re: Can a query find info X numbr of rows from target info?
- References:
- Prev by Date: Re: How to say "when" in an expression
- Next by Date: Re: Can a query find info X numbr of rows from target info?
- Previous by thread: Re: Can a query find info X numbr of rows from target info?
- Next by thread: Re: Can a query find info X numbr of rows from target info?
- Index(es):
Relevant Pages
|
