compare records

  • Thread starter Thread starter Chieko Kuroda
  • Start date Start date
C

Chieko Kuroda

Hello Everyone,
I'd like to know if there's an easy way to compare the a field of the
current record to a different field of the next record. You see, I have a
table with customer ID's, beginningDate, and endingDate. Each customer can
have multiple beginning and ending dates, and I would like to compare the
ending date with the next future beginning date and only that one particular
date; not all of the future beginning dates. Is there a way to use the query
designer to do this? Or, do I have to create a function for this?
Thanks,
Chieko
 
Dear Chieko:

Yes, it is possible to do this, provided you provide an unambiguous
definition of which record is the "next record".

I take it you would define all the rows with the same customer ID as
being the set of rows from which to draw the next record.

The rows within each set might be ordered by either beginningDate or
by endingDate. Please note that, for this to work, the rows within
each customer ID set must be uniquely ordered. This is essential in
order for there to exist one and only one "next record." Otherwise,
you have no unambiguous definition for what is the "next record."

The query is constructed as a "correlated subquery" using aliases for
the table.

If you'll consturct a query that shows all the fields you want to see
except the value from the "next record" then I can probably take it
from there. I need to know what column uniquely orders the rows
within each customer ID value, and the name of the column from the
"next record" you want to see.

Just post the SQL and your answers back here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for your time and effort.
The query would look like this:
Select customerID, enddate from customers.
The CustomerID EndDate and StartDate uniquely identify the row.
the name of the column from the "next record is StartDate
Thanks again,
Chieko
 
Dear Chieko:

Some of the information I asked for was not provided, specifically,
which column in the "Next Record" is the one you want to compare. I'm
guessing this is probably the BeginningDate.

So, here's a query that MAY be what you're looking for:

SELECT CustomerID, EndDate,
(SELECT MIN(StartDate) FROM customers T1
WHERE T1.CustomerID = T.CustomerID
AND T1.StartDate > T.EndDate) AS NextBegin
FROM customers T
ORDER BY CustomerID, EndDate

This will show the ending date and the following start date.

Please let me know if this is the right track.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,
Thank you so much for the help.
Yes that is exactly what I was trying to accomplish.
Now I don't get query result that mix up all of the records.
Chieko
 
Back
Top