Calculating date differences from a table?

[pauly]

Hello All,
I have been trying to create a query that extracts data from a table
and calculates the elapsed time between records. The table called
"Imported_table". I need to be able to calculate the difference in
dates when the vin and labourop are the same and not show any other
records except for these and add a new field called days elapsed and
odom elapsed. Refer to rough sample data below. Anyhelp would be
greatly apreciated.

VIN LabourOP Date Odom
123 abcd 1/1/04 125
124 dddd 2/2/04 253
123 abcd 3/1/04 131

[lesperancer]

create a query (qryMinDate) to find the minimum date by vin, labourOp
SELECT imported_table.vin, imported_table.labourOp,
Min(imported_table.date) AS minDate, Min(imported_table.odom) AS
minOdom
FROM imported_table
GROUP BY imported_table.vin, imported_table.labourOp;
then create a query (qryDateDiff) to find the difference
SELECT qryMinDate.vin, qryMinDate.labourOp,
DateDiff("d",[minDate],[imported_table].[date]) AS dateDiff,
[imported_table].[odom]-[minOdom] AS odomDiff
FROM qryMinDate INNER JOIN imported_table ON (qryMinDate.vin =
imported_table.vin) AND (qryMinDate.labourOp =
imported_table.labourOp);
you should be able to paste these into queries to see how they work
I would rename the 'date' to something like 'readingDate' since 'date'
is a reserved word and makes life difficult

I would also question the need to create fields 'days elapsed' / 'odom
elapsed' when they can be derived from the query about... the trick in
database design is to not store calculated data