Time lapsed between two records with same value

[Guest]

Hello:

I have a tabe that looks like this:

CardId Date Time
261 16/08/2005 04:38:48
261 16/08/2005 09:55:20
60512 16/08/2005 04:52:59
60512 16/08/2005 13:27:47
60614 16/08/2005 07:22:26
60616 16/08/2005 07:13:45
60616 16/08/2005 15:44:51

I need to calculate for each unique cardID time lapsed betwen two times
(DateDif).
Card IDs and times are column headings. I would appreciate pointing me to
some approach.. Thank you very much.

 

[Guest]

You may find the answer you are looking for amongst these:

http://www.mvps.org/access/datetime/index.html

jmonty

 

[Guest]

Hello:

I believe, my problem is not how to calculate a date/time difference but how
to get the diffrence from two records that have the same value in card Id
field. Thanks for the link.
danka

 

[Jeff Boyce]

Will you always only have two records for each CardID? If so, one approach
would be to create a Totals query that determines, Grouped By CardID, the
Min(DateTimeValue) and the Max(DateTimeValue). Then in a second query, find
the difference.

I'm curious, though... your example makes it look like you have a field
named "Date" and another named "Time". If so, this is not a good idea for a
couple reasons.

First, "Date" and "Time" are reserved words in Access, so you will only
confuse yourself and Access if you name your fields this way.

Second, Access has a Date/Time datatype that includes both date and time in
a single field. It is much easier to do date/time math with a single field
than when you have to check/combine two fields.

Regards

Jeff Boyce
<Access MVP>

 

[Guest]

Hello Jeff:

Thank you very much for your assistance. It is rather late at night but I
am am starting getting good results. First, as you suggested I created 2
queries:

1st query to find Min and Max values (i renamed time to time1) - this works OK

CardId MinTime1 MaxTime2
261 04:38:48 09:55:20
60512 04:52:59 13:27:47
60614 07:22:26 07:22:26
60616 07:13:45 15:44:51

2nd query to find a difference in time
CardId Expr3
261 5
60512 9
60614 0
60616 8

In some cases I will have only 1 value for the card ID but I can eliminate
it prior to running these two queries. In this example card 60614 returned 0
(one value).

I believe, I don't need to combine date and time since I will be selecting
records for one day only. But my ultimate goal is to select the ones that
have a difference between two times less then 8.5 hours. DateDiff returns
either hours or minutes. I am wondering how I can get hours and minutes in
difference so I can extract records that are less then 8.5 hours?

Thank you very much for your help.

 

[Jeff Boyce]

Take a look at the syntax for the DateDiff() function -- I believe you can
control the units it returns (days, hours, minutes, etc.).

If you calculate the difference in your second query, you could add a
criterion to that query that only returns values > 8.5 hours (or, if
measured in minutes, 60 x 8.5).

I'm still not clear on your "time" field -- what datatype is in the table
holding this value? Hint: if it is a DateTime datatype, you ARE storing a
date, even if you aren't seeing it due to formatting.

Regards

Jeff Boyce
<Access MVP>

 

[Guest]

Hello:

Thanks again. I am back at the task after my morning meetings.

My original file is just a text file that I am reading into MsAccess table.
I have been playing a lot with the whole thing but right now my date is date
format and time is text field. Possibly, I need to combine them into general
date format. I reanamed the fields as you suggested to avoid a conflict
with Access keywords.

In my last query expression I asked to return minutes in DateDiff function
and I am dividing it by 60. In criteria I asked for > then 8.5. This seems
to be working ok.

Expr3: (DateDiff("n",[Mintime1],[MaxTime2]))/60

Query result is:

CardId Expr3
261 5.28333333333333
60614 0
60936 8.4
60959 8.48333333333333
61003 8.4
61006 8.26666666666667


So, it seems that my major objective is met. Now, I need to go back to the
beginning and try to automate the steps. I need to append data from data
text file into access table each morining,make sure that it is not imported
twice, run all the queries and produce a report that would show all Id cards
having less then 8.5 hour time lapsed for the selected by user date. I am
getting there - thanks to you so much.


Thank you very much for your w