querying for a record after a select record

[Dave]

Let's just say that I have a very simple database set up
that tracks sales of lemonade from my stand. I have a
field with quantity of lemonade purchased measured by the
number of cups. I want to know how many cups were
purchased immediately following purchases of 10 cups or
more. (Each purchase is a new record)

I understand I can make a select query with the criteria
field greater than or equal to 10 cups but I do not
understand how to make the query return the results of
the next record?

EX.

Purchase1 = 5 cups
purchase2 = 8 cups
purchase3 = 12 cups
purchase4 = 3 cups
purchase5 = 2 cups


The result should be: 3 cups

I understand that records are like "ice cubs" in a glass
of lemonade and there is no apparent order to them,
however, since being the creator of the database and
knowing that having some sort of order to these ice cubes
is important to me of course there is a field that
creates order to them by sequence of occurance in
relation to one another. I am still looking for someone
who knows how to tell me how to find a record immediately
following a select record.

Thanks for the help
Dave

 

[Tom Ellison]

Dear Dave:

As long as you have a column, or set of columns, that can be used to
determine an ordering of the rows in the table (or query) then you have a
definition for "next record" given that ordering. Whether this ordering is
unique or may have duplicates can be a significant question in determining
how the results will appear.

The technique I will describe involves using a subquery. This subquery will
find the "next record" using the specified ordering and could then return
the number of cups from the row that has been determined. That "next
record" we will define as the row (or rows) having the minimum value of the
ordering column (PurchaseNumber) considering only those rows with a
PurchaseNumber greater than the one already found, being one with >= 10
cups.

The SQL may look like this:

SELECT
(SELECT Cups FROM YourTable T1
WHERE PurchaseNumber = (SELECT MIN(PurchaseNumber)
FROM YourTable T2 WHERE PurchaseNumber > T.PurchaseNumber)) AS
FollowingCups
FROM YourTable T
WHERE Cups >= 10

The technique above involves two nested levels of subqueries. The inner
level of subquery references the outer, or main query. The criteria of each
of these subqueries references a value from an outer query level, a
technique called correlation.

As I understand it, the Access Jet engine does not support correlation of
subqueries except when the correlation is to a query only one level above
the subquery. Since our innermost subquery above references the main query,
which is two levels away, Jet may not be able to run this. If you are using
Access MSDE for your engine, this would work well.

 

[Dale Fye]

Another technique that would work (I'll assume as Tom did that you
have a unique PurchaseNumber, and will add the assumption that these
are sequential. If your purchase numbers are not sequential, this
will not work, or will leave blanks.) would be.

SELECT T1.PurchaseNumber, T1.Cups, T2.Cups as NextPurchase
FROM yourTable T1
LEFT JOIN yourTable T2
ON T1.PurchaseNumber = T2.PurchaseNumber - 1
WHERE T1.Cups >= 10

--
HTH

Dale Fye


Let's just say that I have a very simple database set up
that tracks sales of lemonade from my stand. I have a
field with quantity of lemonade purchased measured by the
number of cups. I want to know how many cups were
purchased immediately following purchases of 10 cups or
more. (Each purchase is a new record)

I understand I can make a select query with the criteria
field greater than or equal to 10 cups but I do not
understand how to make the query return the results of
the next record?

EX.

Purchase1 = 5 cups
purchase2 = 8 cups
purchase3 = 12 cups
purchase4 = 3 cups
purchase5 = 2 cups


The result should be: 3 cups

I understand that records are like "ice cubs" in a glass
of lemonade and there is no apparent order to them,
however, since being the creator of the database and
knowing that having some sort of order to these ice cubes
is important to me of course there is a field that
creates order to them by sequence of occurance in
relation to one another. I am still looking for someone
who knows how to tell me how to find a record immediately
following a select record.

Thanks for the help
Dave