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
"Ann Scharpf" wrote:
> 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" <(E-Mail Removed)> wrote in message
> > news:FC2D4249-5AE7-4818-94BC-(E-Mail Removed)...
> > > 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
> >
> >
> >
|
Similar Threads
