J
jer
Some help please. I have an original log as follows:-
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes to move
from 1 station to the next (that is from old value to new value. From the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24 minutes.
I have gotten help with the following.
SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value], Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));
I thought this was working as expected but upon closer examination of the
query results (and reports) some information is distoretd. I am not sure how
to inclued the last move. The original source of the log is an excel file,
so before importing into access I added a row of data and changed the values
of the time, old and new values as follows
NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4
*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log where
each name moves approximately through 6 stations
any suggestions or would be appreciated
NAME TIME OLD VALUE NEW VALUE No
W53 3/20/2006 10:45 STATION1 STATION2 1
W53 3/20/2006 10:46 STATION2 STATION3 2
W53 3/20/2006 16:10 STATION3 STATION4 3
W54...
and I am trying to write a query that would return the time it takes to move
from 1 station to the next (that is from old value to new value. From the
above W53 stayed at station 2 - 1 minute, station 3 - 5 hours and 24 minutes.
I have gotten help with the following.
SELECT DISTINCT Log.Date, Log.[Old Value], Log.[New Value], Log.[Date/Time],
Log_1.[Date/Time], Log.Name, Log.No,
DateDiff("s",[Log].[Date/Time],[Log_1].[Date/Time]) AS Seconds
FROM Log, Log AS Log_1
WHERE (((Log.[New Value])=[Log_1].[OLD Value]));
I thought this was working as expected but upon closer examination of the
query results (and reports) some information is distoretd. I am not sure how
to inclued the last move. The original source of the log is an excel file,
so before importing into access I added a row of data and changed the values
of the time, old and new values as follows
NAME TIME OLD VALUE NEW VALUE No
W5330 3/20/2006 10:45 STATION1 STATION2 1
W7211 3/20/2006 10:46 STATION2 STATION3 2
W5101 3/20/2006 16:10 STATION3 STATION4 3
W5101 3/20/2006 21:34* STATION4 END 4
*3/20/2006 16:10 + (3/20/2006 16:10-3/20/2006 10:46)
Even with this some information is distorted. This is a daily log where
each name moves approximately through 6 stations
any suggestions or would be appreciated