John said:
Then your table is not in normal form. A properly normalized Table's
fields depend ONLY on that record's primary key, not on any other
record. There is no such thing as a "previous record" or "last
record", any more than there is a "previous potato" or "last potato"
in a wheelbarrow full of potatoes.
I'm sorry, no, it doesn't make sense at all. I can't see how you
decide what's a vlid value for Field2 - it must be greater than some
other record's value of Field2 if Field1 is zero, and must be zero if
field1 is zero?
What's the REAL LIFE entity being modeled by this table?
John W. Vinson[MVP]
It didn't make lots of sense to me, either, but I did find a way to do
what I think you asked about.
Note: Your description and your example didn't match very well -- you
talked about a record in which [Field 1] = 7, but the only values you
showed in the example for [Field 1] were 0 or 1.
By "previous" you may have meant the record one earlier in the current
sorting order, or the record that was most recently changed or added. In
either case, you can set up a sorting order by including an indexed field,
and in my following example that field is [tblStudents].[WhenAdded],
representing some date (I'm assuming no duplicate dates here) by which the
list of students can be ordered.
So my Table, called [tblStudents], contains the following records:
tblStudentsID Name WhenAdded
------------- ----- ---------
2 Jane 9/2/2005
4 Marv 9/6/2005
7 Greg 9/7/2005
8 Sally 9/1/2005
I define a query [Q_PrevStudentDate] as...
SELECT tblStudents.tblStudentsID, Max(tblStudents_1.WhenAdded)
AS MaxOfWhenAdded
FROM tblStudents, tblStudents AS tblStudents_1
WHERE (((tblStudents_1.WhenAdded)<[tblStudents].[WhenAdded]))
GROUP BY tblStudents.tblStudentsID, tblStudents.Name,
tblStudents.WhenAdded;
and when run it produces these records:
MaxOfWhenAdded tblStudentsID
-------------- -------------
9/1/2005 2
9/2/2005 4
9/6/2005 7
If [Q_tblStudents] is defined as ...
SELECT tblStudents.Name, tblStudents_1.Name AS PreviousStudent
FROM (tblStudents LEFT JOIN Q_PrevStudentDate
ON tblStudents.tblStudentsID = Q_PrevStudentDate.tblStudentsID)
LEFT JOIN tblStudents AS tblStudents_1
ON Q_PrevStudentDate.MaxOfWhenAdded = tblStudents_1.WhenAdded
ORDER BY tblStudents.Name;
then running it produces the following list, in which each student's name
is associated with the one ahead of him in line:
Name PreviousStudent
---- ---------------
Greg Marv
Jane Sally
Marv Jane
Sally
Sally, being first in the list, has no predecessor, which is why I used a
LEFT OUTER JOIN instead of an INNER JOIN in the SQL; otherwise, she
wouldn't have been listed.
Incidentally, besides wondering why you'd want to keep track of things in
this way, I also wonder why you'd want to put the results into a Table.
That would mean that updating one record could make other records become
invalid. A Query like the one I showed you here would be immune to that
kind of trouble.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.