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

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

Relevant Pages

  • Re: SQL Express - Identity specification property - how to change
    ... FOO_ID int identity, ... Tony Rogerson, SQL Server MVP ... They are making unfortunately frequent use of the IDENTITY column in conjunction with Primary Key. ... IDENTITY has none of the properties of a data type because it is not a data type at all. ...
    (comp.databases.ms-sqlserver)
  • Re: Binding and adding rows with SQL identity column
    ... Each time you call AddNew then the DataTable generates a new ... Each time an update is done then the real primary key ... > generated by the built-in wizard in VS2003. ... > identity column on the sql table, perhaps this would all the fine and I ...
    (microsoft.public.dotnet.framework.windowsforms)
  • Re: What happens after 2^32 autonum keys are exhausted?
    ... Tony Rogerson, SQL Server MVP ... generated primary key despite the risk of a hardware or software error or a fault in the space-time continuum. ... identifier and have to invent one, follow the data design (data ...
    (comp.databases)
  • Re: sliced find in Linq
    ... by over all of the returned columns. ... Since the original query was unordered, I'd like the above SQL to ... (Perhaps your table doesn't have a primary key though?) ... didn't specify the ordering, so any ordering could have been used. ...
    (microsoft.public.dotnet.languages.csharp)