select previous and next records

[Guest]

I have a query that selects records based on the data in two fields. I need
to be able to add to that list the previous and next records of each of these
records based on the PK which is an autonumber field. How do I accomplish
this?
Thanks!

 

[louisjohnphillips]

I have a query that selects records based on the data in two fields. I need
to be able to add to that list the previous and next records of each of these
records based on the PK which is an autonumber field. How do I accomplish
this?
Thanks!

This problem appears to call for three queries with the results set of
each combined into one. Use the union operator to do this.

Examine these queries as a possible solution. The first query seeks
the specific row. The second and third queries
retrieve the adjacent rows. The queries assume that the autonumber
field increments by one and that no rows have been deleted from the
table.


SELECT ID_PK, Field1, Field2
from MyTable
Where Field1 = variable1
and Field2 = variable2
union
SELECT ID_PK, Field1, Field2
from MyTable
where ID_PK = (SELECT ( ID_PK - 1 )
from MyTable
Where Field1 = variable1
and Field2 = variable2 )
Union
SELECT ID_PK, Field1, Field2
from MyTable
where ID_PK = (SELECT ( ID_PK + 1 )
from MyTable
Where Field1 = variable1
and Field2 = variable2)

 

[John W. Vinson]

I have a query that selects records based on the data in two fields. I need
to be able to add to that list the previous and next records of each of these
records based on the PK which is an autonumber field. How do I accomplish
this?
Thanks!

I see that Louis John has given you some SQL which should work - but let me
throw in a big red flag warning here. It seems that you are assuming that
Autonumbers will be assigned sequentially and will never have gaps. That
assumption is almost certainly *wrong*; if anyone manually enters records into
the table, deleting a record or hitting <Esc> while entering a record will
leave a gap; if you use Append queries you can get gaps, sometimes large ones.

If your data design depends on the sequence of records in the table a) it
probably shouldn't and b) you may want to use something other than an
autonumber to define that sequence.

John W. Vinson [MVP]

 

[Marshall Barton]

I have a query that selects records based on the data in two fields. I need
to be able to add to that list the previous and next records of each of these
records based on the PK which is an autonumber field. How do I accomplish
this?

You are making an invalid assumption here. Autonumber
fields are not guaranteed to be monotonically increasing,
much less consecutive. The only thing you can say about an
autonumber PK field is that it is unique. Beyond that you
should work with them as if it is a possibly negative random
number.

Based on that, you need to use one or more non-autonumber
fields to specify your definition of "next" and "previous"
records. Most often this is done by having a "date created"
field in the table. For new records, this can be done
simply by adding the field to the table and setting its
DefaultValue property to Now()

With this arrangement, you can use a query like:

SELECT TOP 3 table.*
FROM table
WHERE table.datecreated >=
(SELECT Max(Y.datecreated)
FROM table As Y
WHERE Y.datecreated
< (SELECT X.datecreated
FROM table As X
WHERE X.field1 = value1 And X.field2 = value2))
ORDER BY table.datecreated ASC

If you are trapped in your situation and must accept your
unreliable assumptions about autonumber fields, then replace
the datecreated field in the query with your autonumber
field.

 

[Guest]

I have taken your SQL and inserted my fields but when I try to run it I get a
syntax error. in query expression 'TripDetailsID = (Select(TripDetailsID -
1)) From tblTripDetails Where State = "sc" And MileageDescription = "Fuel".
Here is the SQL:
SELECT TripDetailsID, State, MileageDescription
from tblTripDetails
Where State="sc" AND MileageDescription="Fuel";

union
SELECT TripDetailsID, State, MileageDescription
from tblTripDetails
where TripDetailsID = (SELECT ( TripDetailsID - 1 ))
from tblTripDetails
Where State="sc" AND MileageDescription="Fuel";
Union
SELECT TripDetailsID, State, MileageDescription
from tblTripDetails
where TripDetailsID = (SELECT (TripDetailsID + 1))
fromtblTripDetails
Where State="sc" AND MileageDescription="Fuel";
Thanks again!

 

[Guest]

Thanks John for your reply. From looking at my table all the PK #'s appear
to be sequential. I understand what you're saying about adding or deleting
records causing these numbers not to be consecutive. What I'm trying to do
here is a one time retrieval of some information for a special circumstance.
I'm trying both SQLs posted to try to select the proper records.
Many thanks for your help!

 

[Guest]

I've tried your SQL using my names but when I try to run it I get a message
saying "At most one record can be returned bt this subquery". I'm also
trying the SQL posted by Louis John but am running into a syntax error there.
Thanks so much for your help!

 

[John Spencer]

PK = Primary Key Field
Assuming that the primary key field has no gaps in the autonumbering,
the following may work for you

SELECT TA.PK
FROM YourTable as TA
WHERE Field1 = CriteriaOne and Field2 = Criteria2
Or PK+1 in (SELECT PK
FROM YourTable
WHERE Field1 = CriteriaOne and Field2 = Criteria2)

Or PK-1 in (SELECT PK
FROM YourTable
WHERE Field1 = CriteriaOne and Field2 = Criteria2)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[louisjohnphillips]

I've tried your SQL using my names but when I try to run it I get a message
saying "At most one record can be returned bt this subquery". I'm also
trying the SQL posted by Louis John but am running into a syntax error there.
Thanks so much for your help!










- Show quoted text -

The text posted at 11:30 a.m. most certainly has syntax errors.

The three most obvious are:

--There should only be a single semi-colon in the entire query;
--Single quotes should be used to specify literal values;
--and the subqueries should be enclosed with a parenthesis.

It appears more than on row meets the criterion of "Where State='SC'
AND MileageDescription='Fuel'." Therefore, you must choose one for
the query to work.

This becomes a little more complicated, but this code might work.

SELECT TripDetailsID, State, MileageDescription
from tblTripDetails
Where State='SC'
AND MileageDescription='Fuel'
AND TripDetailsID = ( select min( TripDetailsID )
from tblTripDetails
where State = 'SC'
and MileageDescription = 'Fuel' )
union
SELECT A.TripDetailsID, A.State, A.MileageDescription
from tblTripDetails as A
Where State='SC'
AND MileageDescription='Fuel'
AND TripDetailsID = ( select max( TripDetailsID )
from tblTripDetails
where State = 'SC'
and MileageDescription = 'Fuel'
and TripDetailsID < A.TripDetailsID )
Union
SELECT A.TripDetailsID, A.State, A.MileageDescription
from tblTripDetails as A
where TripDetailsID = (SELECT min(TripDetailsID )
from tblTripDetails
Where State='SC'
AND MileageDescription='Fuel'
and TripDetailsID > A.TripDetailsID )

This problem may be needlessly complicated because the TripDetailsID
only approximates the sequence that rows entered the table. The query
might return a more meaningful result if it sought the rows based on
the date of the trip.

 

[Guest]

There are multiple records (754) that meet both criteria. I am trying to
find all records where fuel was bought at a certain location so I was trying
to narrow it down to that state to start with. If I use only one criteria to
start with and then add the adjacent records I can select further from there
or are you saying that I would only be able to end up with 3 records?

 

[Marshall Barton]

That error message indicates that you have multiple records
that match the criteria on those two fields. If you want to
use the lowest or highest PK, then use Min or Max

. . .
. . . < (SELECT Max(X.datecreated)
FROM table As X
WHERE X.field1 = value1 And X.field2 = value2))
. . .

 

[louisjohnphillips]

That error message indicates that you have multiple records
that match the criteria on those two fields. If you want to
use the lowest or highest PK, then use Min or Max

. . .
. . . < (SELECT Max(X.datecreated)
FROM table As X
WHERE X.field1 = value1 And X.field2 = value2))
. . .

I've tried your SQL using my names but when I try to run it I get a message
saying "At most one record can be returned bt this subquery".

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Perhaps Loni can describe the problem further. It would be helpful to
know the other data elements in the table. My earlier presumption was
that at most three rows of output were desired for each State-
MileageDescription combination
..

 

[John W. Vinson]

I have a query that selects records based on the data in two fields. I need
to be able to add to that list the previous and next records of each of these
records based on the PK which is an autonumber field. How do I accomplish
this?
Thanks!

Reading over the thread, perhaps this would work:

SELECT TripDetailsID, State, MileageDescription
from tblTripDetails
Where TripDetailsID IN
(SELECT TripDetailsID FROM tblTripDetails AS X WHERE X.State="sc" AND
X.MileageDescription="Fuel")
OR TripDetailsID IN
(SELECT TripDetailsID-1 FROM tblTripDetails AS X WHERE X.State="sc" AND
X.MileageDescription="Fuel")
OR TripDetailsID IN
(SELECT TripDetailsID+1 FROM tblTripDetails AS X WHERE X.State="sc" AND
X.MileageDescription="Fuel");

Don't expect this query to run lightning quick...

John W. Vinson [MVP]

 

[Guest]

Many, many thanks John! That did the trick.

Reading over the thread, perhaps this would work:

SELECT TripDetailsID, State, MileageDescription
from tblTripDetails
Where TripDetailsID IN
(SELECT TripDetailsID FROM tblTripDetails AS X WHERE X.State="sc" AND
X.MileageDescription="Fuel")
OR TripDetailsID IN
(SELECT TripDetailsID-1 FROM tblTripDetails AS X WHERE X.State="sc" AND
X.MileageDescription="Fuel")
OR TripDetailsID IN
(SELECT TripDetailsID+1 FROM tblTripDetails AS X WHERE X.State="sc" AND
X.MileageDescription="Fuel");

Don't expect this query to run lightning quick...

John W. Vinson [MVP]