Dear Roger:
It is just possible that you might now have, or might someday have, an
instance where these rows may not always sort the same by recID as
they do by dtePerf. I'm going to assume that the elpsdDays should be
performed using the dtePerf sort, and build the query accordingly.
SELECT recID, dtePerf,
DateDiff("d", (SELECT MAX(dtePerf) From YourTable T2
WHERE T2.dtePerf < T1.dtePerf), dtePerf) AS elpsdDays
FROM YourTable T1
ORDER BY dtePerf
You must substitute the actual name of your table, or query, in the
above.
Note that, if there are ever two rows with the same dtePerf, then
there is no way to distinguish which one comes first. Using my logic
above, they will both be the same elpsdDays after whatever date
precedes them.
A more complex query could use the recID to arbitrarily improve this
situation, assuming recID is unique, at least for any single value of
dtePerf (that is recID and dtePerf are a unique composite key).
SELECT recID, dtePerf,
DateDiff("d", (SELECT MAX(dtePerf) FROM YourTable T2
WHERE T2.dtePerf < T1.dtePerf
OR (T2.dtePerf = T1.dtePerf AND T2.recID < T1.recID))
AS elpsdDays
FROM YourTable T1
ORDER BY dtePerf, recID
Whichever way you do it, your dtePerf, or he combination of dtePerf
and recID must be unique or your results will probably not be what you
want.
For example, using your sample data, I add one more row:
This is what the first query above would return, although it might
also return:
1 01/01/04 Null
2 01/03/04 2
3 01/06/04 3
5 01/07/04 1
4 01/07/04 1
For the same data, the second query would return:
1 01/01/04 Null
2 01/03/04 2
3 01/06/04 3
4 01/07/04 1
5 01/07/04 0
That may be more like you expect. However, the second query will have
"unexpected" difficulty with this:
1 01/01/04 Null
2 01/03/04 2
3 01/06/04 3
4 01/07/04 1
4 01/07/04 1
Simply because the row from the 6th is the MAX() of all dates less
than the one on the seventh, given that both have the same recID.
Uniqueness of rows in a table or query is what gives them an
"identity". Without "identity" there is no way for your queries to
distinguish between them. Having two identical rows in a table does
not in any way allow you to ever make them behave differently. This
is what is meant by a "bag". Even though all the rows in a table are
always presented in some sequence, that is always a sequence that is
imposed on it by some ordering. Lack of uniqueness means that there
can be rows that are indistinguishable in their order. When you query
such information, these indistinguishable rows CANNOT result in
different results in the query. They are absolutely interchangeable.
One way to look at it is this. If you allow indistinguishable rows in
a table, then you may as well just combine all the identical rows into
one, and add a Count column to the table that says how many of them
you have. I'm dead serious! Lack of identity destroys a large part
of the usefulness of your data. But, until you have a query need like
yours, you may not realize it.
Assuming your recID is an autonumber, this gives you an arbitrary way
of ordering the rows in a table, which can be useful as I hope I have
shown. If you now have, or ever add more columns to the table that
may allow you to distinguish between the rows that are currently
identical, you may find that the arbitrary order imposed by the
autonumber values is not really what you intended. It can become
quite arbitrary.
I make it a rule not to design tables without a "unique natural key"
(one that does not contain a hidden identity number such as an
autonumber).
Well, that's not quite a chapter of a book yet, but maybe I'll write
it sometime. I think some of this may be something you need to know
to understand the behavior of your data when querried.
If you've read this far, maybe I should thank you!
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
