By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Calculating date differences from a table?

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.