Calculating difference between field values for different records

[SteveS]

I am working with a query in which I want to determine the elapsed time
between date values in different records. The application is a database
containing animal records. I have a table that contains birth information.
Each record contains the data regarding a birth event (the animal giving
birth, data of birth, etc.). I want to be able to calculate the elapsed time
between the recent birth and the previous one for each female in the table.
This table may have multiple records for a given female, but I only want the
time between the most recent birth and the previous birth.

How can I add a field to my query that will identify that previous birth
record? If I can do that, I can easily calculate the difference between the
latest birth and the immediately prior birth. Other approaches are welcome
as well, but basically, the problem is that I need a self join with this
table, and the ability of match up the most recent birth record with the
immediately prior birth record.

Thanks for the help.

 

[Allen Browne]

If you don't mind that the results are read-only, a subquery will be the
quickest way to get this info.

Type something like this into the Field row in query design:
PriorDate: (SELECT Max([EventDate]) FROM Table1 AS Dupe
WHERE (Dupe.AnimalID = Table1.AnimalID)
AND (Dupe.EventDate < Table1.EventDate))

Replace "Table1" with the name of your table, etc. The 2nd copy of the same
table is aliased as Dupe, and then the field names of the Dupe copy are
matched to the field names in the main query.

If the results must be editable, you could use DMax() to get the last date.