Time Difference Calculation

[boaterjohn]

I have a query that has 4 fields.- " Date, CarNo, TimeStopped and TimeDifference.
The query is filtered useing the "[Enter Date]. The "TimeStoped" field and the "TimeDiff" field are short time format. An example of what my select qry looks like:
RecNo Date CarNo TimeStopped TimeDifference
1 02/25/2013 1 09:15 1st time needszero
2 02/25/2013 1 09:30 rec2-rec1
3 02/25/2013 1 10:00 rec3-rec2
4 02/25/2013 2 09:15 1st time needszero
5 02/25/2013 2 09:30 rec5-rec4
6 02/25/2013 2 09:45 rec6-rec5
and so forth.

I need to calculate the "TimeDifference" field. I know I need a recordset for the query and some sort of looping code. I'm pretty green in this coding. If anyone can give me some help; I really thank you.
boaterjohn

 

[thechazm]

I see you referenced a TimeStoped field but is their a TimeStarted field or are you comparing it to the Enter Date?

 

[pbaldy]

This should get you started:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[boaterjohn]

I see you referenced a TimeStoped field but is their a TimeStarted field or are you comparing it to the Enter Date?

This TimeDifference Calculation will be done on a daily basis at 18:00.
The next day, a new query will be made using the daily date only to extract the times for that day. As you can see in my table, I need code to loop thur the daily records for that day. The query will sort the CarNo and TimeStopped fields in assending order. The calculation code will ie.... for car1 subtract record 2 timestopped from 1 timestopped then
car1 subtract record 3 timestopped from 2 timestopped.. Note,
record 1 of each CarNo will always be zero because there is nothing to subtract it from.
Thanks for the reply
boaterjohn

 

[thechazm]

Pbaldy posted a really nice reference but maybe it's just as simple as opening two recordsets to the same data and by doing that you can retrieve the data necessary to produce the time difference.

Eg. not tested typed in post

dim db as database, rs as dao.recordset, rs2 as dao.recordset
dim ii as long, firstpass as boolean
set db = currentdb
 
set rs = db.openrecordset("Select * from [Your Query name] WHERE((([Enter Date]) = #" & Date & "#));", dbopendynaset)
 
set rs2 = db.openrecordset("Select * from [Your Query name] WHERE((([Enter Date]) = #" & Date & "#));", dbopensnapshot)
 
rs.movelast
rs.movefirst
ii = rs.recordcount
firstpass = true
 
do not while rs.eof
        ' Find the smallest record number
if firstpass = false then
        ' Then on rs2 goto the next record number like something like the following
        rs2.findfirst "[RecNo] = " & rs("RecNo") + 1
        if not rs2.nomatch then
                 with rs
                          .Edit
                          ![Time TimeDifference] = rs2("TimeStopped") - rs("TimeStopped")
                          .Update
                 End With
         End If
else
       firstpass = false
end if
         rs.movenext
loop
 
rs.close
rs2.close
set rs = nothing
set rs2 = nothing
set db = nothing

It's not perfect but it should be close.

 

[boaterjohn]

Thank You Kindly thechazm.
I will have to digest this tomarrow and will let you know how I make out.
pbaldy gave a nice link as you said. I checked it and it seemed to go on date links of the database which gave 2 readings on different dates. This would have been great; but, as you can see in my example, I have only the current date and all the stops that car 1 made and car 2 made.

I wish I had a timestopped 1 and a other field timestarted 1; however, I only have the one field "TimeStopped" to subtract one record from the record above to see how much "TimeDifference" was spent between every "TimeStops". BTW. I do have it working one record at a time using and "Event Button". Right now I have to manually loop thru each record of the form based on the query and click the "event button".

Now my objective is to use the query the same way except have the procedure run thru all the records by calling the proc.

Thanks again for yur input. I'll certainally check the code out.
boaterjohn

 

[pbaldy]

You should be able to use the time field or the record number to use Allen's method, or combine the date and time fields and use that.

 

[CJ_London]

Using your data at the begining of this thread, are you saying for car 1 the query should return three records - 0mins, 15mins and 30mins or one record 45mins?

 

[thechazm]

ROFL man I must have been stupid tired yesterday. CJ London thanks for pointing that out. boaterjohn I totally did not notice that. While the code is still close that I wrote earlier it's not going to give you it per car. I'll shoot you the code that will do this later today. Good luck

 

[CJ_London]

Not sure I got the answer I was looking for but here are two queries which shgould do what you want:

This one will give you the difference between the timestopped and the previous record

SELECT DISTINCT CarNo, TDate, TimeStopped, [Timestopped]-Nz((Select Max(timestopped) from table1 as tmp where tdate=table1.tdate and carno=table1.carno and timestopped<table1.timestopped),0) AS Result
FROM Table1 WHERE TDate=[Enter Date]

This one will give you the difference between the first and last records of the day

SELECT DISTINCT Table1.CarNo, Table1.TDate, Table1.TimeStopped, [Timestopped]-Nz((Select Min(timestopped) from table1 as tmp where tdate=table1.tdate and carno=table1.carno and timestopped<table1.timestopped),0) AS Result
FROM Table1
WHERE (((Table1.TimeStopped) In (select max(timestopped) from table1 as tmp2 where carno=table1.carno and tdate=table1.tdate)) AND ((Table1.[TDate])=[Enter Date]))

I created Table1 and populated it with your example data fromn the first post, so you will need to modify accordingly

See how you get on

 

[thechazm]

Looks like CJ beat me to it awwww

 

[CJ_London]

- shucks

 

[boaterjohn]

CJ_London,
Thank you much for your time and qry. The 1st one that will give you the difference between the timestopped of the current record and the previous record is the one I need. However, did you use just one query and if so, how did set the qry up to preform the calculation?
boaterjohn

 

[CJ_London]

Hi Boaterjohn,

It is just one query, but uses a subquery - open in design view to see how it works

SELECT DISTINCT CarNo, TDate, TimeStopped, [Timestopped]-Nz([COLOR=red](Select[/COLOR] [COLOR=red]Max(timestopped) from table1 as tmp where tdate=table1.tdate and carno=table1.carno and timestopped<table1.timestopped)[/COLOR],0) AS Result
FROM Table1 WHERE TDate=[Enter Date]

The bit in red is the subquery which is effectively the calculation and is exactly the same as an 'ordinary' query but with two exceptions.

• 'as tmp' is added to differentiate it from table1 in the main query
• 'and carno=table1.carno and timestopped<table1.timestopped' is the join between table1 in the main query and the tmp table in the sub query

Sub queries are very useful and it is worth you learning more about them

Glad I could help, please tick the thanks box if not already done so!

 

[boaterjohn]

Thanks CJ_London,
I'm a little green in queries and have never used a sub-query, but have used sub-forms. My issue is this, I have a form with only the 5 fields as you saw in my original post. At the end of the day, I enter 15 to 20 records in the form; filling in the first 4 fields. The last field being the "TimeDifference" field. I then want to click a "Event Butn" which will run ie.. the query to fill in all the blank "TimeDifference" fields. The records are sorted by "CarNo" and "TimeStopped". Therefore, CarNo 1 will be the 1st 8 records, CarNo 2 will be the next 5 records and finally CarNo 3 will be the last 7 records. The first record of each CarNo will always be zero (0) because there is nothing to subtract from that record. Hope I made sense of this.
Gratefully Thankful,
boaterjohn

 

[CJ_London]

Umm,

You don't need a query to do that, you use dmax instead and it will update as you go so no need for a button

I'm a bit confused - appreciate the overall objective is the same, but this is different from what you originally asked. You originally had a query, but now you have an an input form for filling in the data and you were updating a table with the results. Since this is a different table it will have to be appear on a separate form or subform.

If you want to see the results as you go, in the TimeDifference control controlsource enter the following

=[TimeStopped]-nz(dmax("TimeStopped","TableName","[tdate]=" & [tdate] & " and [carno]= " & [carno] & " and [timestopped]<" & [timestopped]),[TimeStopped])

I don't have your details to hand so you will need to correct TableName and any of the fields you have on the form.

I suggest you review what you are doing and experiment yourself a bit

 

[boaterjohn]

CJ_London,
attached is my very small access database. Feel free to see exactly what I'm trying to do. I think I might have misled you in how I discribed what I was after.
Thanks Much,
boaterjohn

 

[CJ_London]

To be honest - you appear to have set this up in a much more complicated way than is required.

Presumably this is a sub set of the whole system and I am not keen to change things since it may stop working with the bits you haven't shown me.

For some reason your input form is working off a grouped query which makes control of input complicated - why not just input direct to the table?

I suggest you review that you are trying to do and make the changes necessary.

The routines I provided will work equally well in a report so I suggest:

• use your tdailytimestops table as the recordsource for your form.
• drop the timediff from the table
• change your form to dataentry to match what you have as a form layout
• lose the timediff field since it is calculated
• lose the two queries
• incorporate the routine I have provided into your report

Sorry I can't be more help - this is something you need to do yourself

 

[boaterjohn]

Thank You so much CJ_London for all your time and input. I have been reading up on sub-queries and will approch that route to do the calculation of "TimeDff".
Regards,
boaterjohn