Calculate elapsed time

[bob1]

I want to create a query that shows elapsed time from the previous record and
I'm having a lot of difficulties....

The fields I have are
User ID, Case#, Units, Location, Date/Time

I want to enter a start and end date range (MM/DD/YY HH:MM:SS AM/PM) then
enter the a User ID and have the query pull of all the activities between the
time range. I already have this query but I want to add elapsed time to the
query to show the amount of time elapse from previous record.

The reason I am making a query based on a query is that that the original
query has many many criteria to get the information that I need. Also the
table shows the Date/Time in one field. So basically the results that I need
are.
User ID, Case #, Units, Location, Date/Time, Elapsed Time.

Thank you for your help!!

 

[Allen Browne]

Use a subquery to get the value from the previous row. Details in:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord

Once you have that working, use DateDiff() to calculate the number of
seconds (or minutes) between the 2 values:
Calculating elapsed time
at:
http://allenbrowne.com/casu-13.html

 

[John Spencer]

So, do you want the previous time by user and case and unit and location?
Or by user and case
Or by case

Assuming that you want it for user, case, unit, and location you could use a
query like

SELECT A.[User ID], A.[Case#], A.Unit, A.Location, A.[Date/Time]
, Max(B.[Date/Time]) as PreviousTime
, DateDiff("n",Nz(Max(B.[Date/Time]),A.Date/Time),A.[Date/Time]) as
NumSeconds
FROM [YourTable] as A LEFT JOIN [YourTable] as B
ON A.[User id] = B.[User id]
and A.[Case#] = B.[Case#]
AND A.Unit = B.Unit
AND A.Location = B.Location
AND A.[Date/Time] > B.[Date/Time]
WHERE A.[Date/Time] Between #2007-01-01# and #2007-12-31#
GROUP BY A.[User ID], A.[Case#], A.Unit, A.Location, A.[Date/Time]

The above query cannot be built in query design view (the query grid), but
must be constructed in the SQL view.

You could use the DMax function as a calculation in your Current query.
Assuming you wanted the prior time for a specific USER ID, the DMax function
would look something like the following. I assumed that User ID was a text
field. If it is a number field, turn the three quotes into one and delete
the four quotes

Field: Previous: DMAX("[Date/Time]","YourTableName","[Date/Time]<#" &
[Date/Time] & "# AND [User id] = """ & [User Id] & """")


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[bob1]

Field: Previous: DMAX("[Date/Time]","YourTableName","[Date/Time]<#" &
[Date/Time] & "# AND [User id] = """ & [User Id] & """")

This seems to be taking me in the right direction, but if for the User ID
field I have a criteria of [Enter User ID] How would I change it to
accomodate it.

and Thank you very much.

So, do you want the previous time by user and case and unit and location?
Or by user and case
Or by case

Assuming that you want it for user, case, unit, and location you could use a
query like

SELECT A.[User ID], A.[Case#], A.Unit, A.Location, A.[Date/Time]
, Max(B.[Date/Time]) as PreviousTime
, DateDiff("n",Nz(Max(B.[Date/Time]),A.Date/Time),A.[Date/Time]) as
NumSeconds
FROM [YourTable] as A LEFT JOIN [YourTable] as B
ON A.[User id] = B.[User id]
and A.[Case#] = B.[Case#]
AND A.Unit = B.Unit
AND A.Location = B.Location
AND A.[Date/Time] > B.[Date/Time]
WHERE A.[Date/Time] Between #2007-01-01# and #2007-12-31#
GROUP BY A.[User ID], A.[Case#], A.Unit, A.Location, A.[Date/Time]

The above query cannot be built in query design view (the query grid), but
must be constructed in the SQL view.

You could use the DMax function as a calculation in your Current query.
Assuming you wanted the prior time for a specific USER ID, the DMax function
would look something like the following. I assumed that User ID was a text
field. If it is a number field, turn the three quotes into one and delete
the four quotes

Field: Previous: DMAX("[Date/Time]","YourTableName","[Date/Time]<#" &
[Date/Time] & "# AND [User id] = """ & [User Id] & """")


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query that shows elapsed time from the previous record
and
I'm having a lot of difficulties....

The fields I have are
User ID, Case#, Units, Location, Date/Time

I want to enter a start and end date range (MM/DD/YY HH:MM:SS AM/PM) then
enter the a User ID and have the query pull of all the activities between
the
time range. I already have this query but I want to add elapsed time to
the
query to show the amount of time elapse from previous record.

The reason I am making a query based on a query is that that the original
query has many many criteria to get the information that I need. Also the
table shows the Date/Time in one field. So basically the results that I
need
are.
User ID, Case #, Units, Location, Date/Time, Elapsed Time.

Thank you for your help!!

 

[John Spencer]

Well, you could use

Field: Previous: DMAX("[Date/Time]","YourTableName","[Date/Time]<#" &
[Date/Time] & "# AND [User id] = """ & [ENTER User Id] & """")

HOWEVER, that should not be necessary to do since the original expression
was getting the user id from the record that was being processed.

Did you try the original? Did it give you the desired results? If not,
what was wrong with the result it returned?

IF you have more than one table involved you may need to specify the
tablename, so it is clear what you are specifically referring to.

Field: Previous: DMAX("[Date/Time]","YourTableName","[Date/Time]<#" &
[YourTableName].[Date/Time] & "# AND [User id] = """ & [YourTableName].[User
Id] & """")

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Field: Previous: DMAX("[Date/Time]","YourTableName","[Date/Time]<#" &
[Date/Time] & "# AND [User id] = """ & [User Id] & """")

This seems to be taking me in the right direction, but if for the User ID
field I have a criteria of [Enter User ID] How would I change it to
accomodate it.

and Thank you very much.

So, do you want the previous time by user and case and unit and location?
Or by user and case
Or by case

Assuming that you want it for user, case, unit, and location you could
use a
query like

SELECT A.[User ID], A.[Case#], A.Unit, A.Location, A.[Date/Time]
, Max(B.[Date/Time]) as PreviousTime
, DateDiff("n",Nz(Max(B.[Date/Time]),A.Date/Time),A.[Date/Time]) as
NumSeconds
FROM [YourTable] as A LEFT JOIN [YourTable] as B
ON A.[User id] = B.[User id]
and A.[Case#] = B.[Case#]
AND A.Unit = B.Unit
AND A.Location = B.Location
AND A.[Date/Time] > B.[Date/Time]
WHERE A.[Date/Time] Between #2007-01-01# and #2007-12-31#
GROUP BY A.[User ID], A.[Case#], A.Unit, A.Location, A.[Date/Time]

The above query cannot be built in query design view (the query grid),
but
must be constructed in the SQL view.

You could use the DMax function as a calculation in your Current query.
Assuming you wanted the prior time for a specific USER ID, the DMax
function
would look something like the following. I assumed that User ID was a
text
field. If it is a number field, turn the three quotes into one and
delete
the four quotes

Field: Previous: DMAX("[Date/Time]","YourTableName","[Date/Time]<#" &
[Date/Time] & "# AND [User id] = """ & [User Id] & """")


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to create a query that shows elapsed time from the previous
record
and
I'm having a lot of difficulties....

The fields I have are
User ID, Case#, Units, Location, Date/Time

I want to enter a start and end date range (MM/DD/YY HH:MM:SS AM/PM)
then
enter the a User ID and have the query pull of all the activities
between
the
time range. I already have this query but I want to add elapsed time
to
the
query to show the amount of time elapse from previous record.

The reason I am making a query based on a query is that that the
original
query has many many criteria to get the information that I need. Also
the
table shows the Date/Time in one field. So basically the results that
I
need
are.
User ID, Case #, Units, Location, Date/Time, Elapsed Time.

Thank you for your help!!