Calculating time between rows

[Noreene Patrick]

I really like the new look!!!

I am beginning to realize that this is the DB from hell!!!

I am trying to create a query that will calculate time between rows...For instance, my table has pickerid and time they pick up an order (which is time stamped) and I also have a start time for the day (not sure that will be needed),

What I need to do is calculate by pickerid, the number of minutes elasped between each record...They will pick up an order, it is timestamped, and they go to pick it, come back for another and so forth.

By doing this, I can tell how many minutes it took to pick each order...(if an order is 60 minutes and it took them 80 minutes, then I can calculate their efficiency per order).

I cannot figure out how to get it calculate from previous row(or how to even get started)...Could someone help?

Thanks, Noreene

 

[dcx693]

I've posted a few times on how to do something similar. You'll basically need to create a query where you join the copy to an alias of itself. That way, you can compare rows to one another.

I'll do a search on for my posts. I'll post back if I find a useful one.

 

[dcx693]

Here's one:
subtracting numbers in the same field.

 

[Noreene Patrick]

Thanks,

That makes sense in my mind, but I just cant get it together on the grid...could i use something like TimeElasped from previous row? (or something like that!!!)..Or maybe if you explain how to reference the row above...because the autonumber is not in order by pickerid...several pickers could have entered an order...so I grouped by pickerid with pickerid in ascending order...I have them together in the table...now, I just need a column that subtracts previous row by pickerid from next row...but, it has to start all over again when pickerid changes...

I know it is simple for someone (like you), but NOT FOR ME!!!!

Thanks,NOreene

 

[Jon K]

I have attached an example to demonstrate how to get the TimeFromPreviousRow in a query for each PickerID.

However, doing it in a query is not an efficient way if the table is large. For a large table, I would add a TimeFromPreviousRow field in the table and update it using VBA code.

 

[Noreene Patrick]

First of all I would like to thank both dcx693 and Jon K for all your help.

I am attaching a scaled down version of my db for you to look at if you have a chance...What I am needing is for the first record to show starttime,and the next starttime should be the finishtime on first record...meaning, the same time the picker picks up his second order is the same time he finished the first...also, I am only pulling current dates so I dont know if it would be more efficient to run vb code in table (again, that is something I dont know how to do).

One more thing, when a picker shows his last record as starttime, the finishtime should be blank because he will not pick up another order because he has loggedout of the function...that logout is in another table called tbllog. I just need to incorporate that time into the last record for the picker.

That's alot of needs for one person, Huh??? But, I never claim these answers as my own, I always give your forum the credit....You have always helped me...Thanks so much.

Noreene

 

[Jon K]

I have added some records for 24 - 26 February in your table and a table tblLog with pickers' daily LogoutTime.

You can run the new query that I have added. It uses DLookup() to look up LogoutTime from tblLog.


For only some 60 records a day, you don't need to use VBA. When tblLog becomes very large, you can create a query to pull only the current date's logout records from tblLog and modify qryCalculateStartFinish to look up logout times from this new query instead of the large tblLog table.

 

[Noreene Patrick]

I am so excited to see this query working!!!! Although I don't understand the function, it does what I need for it to do..and I am greatly relieved...

Thanks to you, JonK for all your help...and to others that have been so ready to answer...

Noreene

PS This is the best forum I have ever seen!!!!!!

 

[Noreene Patrick]

Jon K

I have just one more question....Why is it when I pull in my logouttime from another table, that the calculation gives me a crazy number like -5447212272
or when I format it for short time, it tries to give me 21 hours when starttime is 8:31 am and timeloggedout is 11:30 am?

I cant seem to hit upon the right combination...could it have something to do with the cdate in formula for finishtime?

In tbllog, the timeloggedin and timeloggedout are in general date to capture time after 12 midnight.

Thank you, NOreene

 

[Jon K]

DMin returns the dates in text strings. CDate just converts the text strings to dates so that they can be displayed in Short Time format.

Depending on a computer's system date format, the # sign (i.e. the date delimiter) may not handle the time around midday and midnight very well. I have included a new query in the database using Format([Time],'yyyymmddhhnnss'). This format doesn't use the system date format. It works correctly in the attached database on my system. Hope it also works on yours.

If the problem persists, please attach a database with the tables tblOrderWindow and tblLog and some records.

 

[Noreene Patrick]

Jon K

You are probably tired of me...I couldnt seem to get it right with new query...It has something to do with my format because yours works....

The problem is when I manually log someone out of the tbllog....I can run the query (I entered a new field to calculate time) and when I hover my mouse over the logout time it doesnt show the same formatted time as the starttime...therefore, the calculation doesnt work right, just on the ones that are logged out from tbllog. Everything else works great..,

I am attaching the db so you can see...Thanks for your patience and help.

Noreene

 

[Jon K]

I changed the # signs to the Format([Time],'yyyymmddhhnnss') in the query.

When I ran the query, I got these results:-

Date		Picker	Wave	Regular	Wave  	Start	Finish 	Time
		ID		Minutes	Minutes	Time	Time	PerOrder
2/25/2004	209	FALSE	85	0	8:27	10:34	127
2/25/2004	209	FALSE	82	0	10:34	12:02	88
2/25/2004	209	FALSE	67	0	12:02	13:10	68
2/25/2004	209	TRUE	0	8	13:10	13:25	15
2/25/2004	209	TRUE	0	5	13:25	13:26	1
2/25/2004	209	FALSE	21	0	13:26	14:17	51
2/25/2004	209	FALSE	5	0	14:17	14:20	3
2/25/2004	209	TRUE	0	72	14:20	15:45	85
2/25/2004	217	FALSE	81	0	8:28	8:31	3
2/25/2004	217	FALSE	29	0	8:31	11:12	161
2/25/2004	217	FALSE	33	0	11:12	12:01	49
2/25/2004	217	FALSE	61	0	12:01	12:46	45
2/25/2004	217	FALSE	94	0	12:46	14:12	86
2/25/2004	251	FALSE	77	0	8:14	9:26	72
2/25/2004	251	FALSE	83	0	9:26	10:45	79
2/25/2004	251	FALSE	71	0	10:45	12:10	85
2/25/2004	251	FALSE	78	0	12:10	13:15	65
2/25/2004	251	FALSE	6	0	13:15	13:19	4
2/25/2004	251	FALSE	11	0	13:19	13:20	1
2/25/2004	251	FALSE	7	0	13:20	14:04	44
2/25/2004	251	FALSE	5	0	14:04	14:13	9
2/25/2004	251	FALSE	47	0	14:13	15:40	87
2/25/2004	309	FALSE	57	0	7:09	8:02	53
2/25/2004	309	FALSE	101	0	8:02	10:19	137
2/25/2004	309	FALSE	43	0	10:19	11:08	49
2/25/2004	309	FALSE	63	0	11:08	12:41	93
2/25/2004	309	FALSE	43	0	12:41	13:06	25
2/25/2004	309	FALSE	81	0	13:06	14:34	88
2/25/2004	327	FALSE	93	0	8:41	10:30	109
2/25/2004	327	FALSE	60	0	10:30	11:45	75
2/25/2004	327	FALSE	60	0	11:45	13:27	102
2/25/2004	327	FALSE	7	0	13:27	13:30	3
2/25/2004	327	FALSE	10	0	13:30	13:45	15
2/25/2004	327	FALSE	12	0	13:45	14:25	40
2/25/2004	327	FALSE	5	0	14:25	14:28	3
2/25/2004	327	FALSE	71	0	14:28	15:57	89
2/25/2004	365	FALSE	64	0	7:08	8:13	65
2/25/2004	365	FALSE	71	0	8:13	9:32	79
2/25/2004	365	FALSE	69	0	9:32	11:03	91
2/25/2004	365	FALSE	81	0	11:03	13:07	124
2/25/2004	365	FALSE	16	0	13:07	13:09	2
2/25/2004	365	FALSE	68	0	13:09	14:39	90
2/25/2004	373	FALSE	64	0	8:33	10:04	91
2/25/2004	373	FALSE	55	0	10:04	10:54	50
2/25/2004	373	FALSE	80	0	10:54	12:20	86
2/25/2004	373	FALSE	74	0	12:20	13:31	71
2/25/2004	373	FALSE	7	0	13:31	13:59	28
2/25/2004	373	FALSE	71	0	13:59	15:30	91
2/25/2004	387	FALSE	68	0	7:50	8:43	53
2/25/2004	387	FALSE	66	0	8:43	11:22	159
2/25/2004	387	FALSE	67	0	11:22	13:42	140
2/25/2004	387	FALSE	14	0	13:42	13:48	6
2/25/2004	387	FALSE	67	0	13:48	15:20	92
2/25/2004	808	FALSE	67	0	7:06	8:18	72
2/25/2004	808	FALSE	78	0	8:18	10:08	110
2/25/2004	808	FALSE	52	0	10:08	11:42	94
2/25/2004	808	FALSE	67	0	11:42	13:46	124
2/25/2004	808	FALSE	33	0	13:46	15:19	93
2/25/2004	829	FALSE	60	0	10:41	12:18	97
2/25/2004	829	FALSE	79	0	12:18	14:21	123
2/25/2004	829	FALSE	65	0	14:21	15:55	94
2/25/2004	836	FALSE	107	0	7:11	10:25	194
2/25/2004	836	FALSE	62	0	10:25	12:04	99
2/25/2004	836	FALSE	85	0	12:04	14:15	131
2/25/2004	836	FALSE	93	0	14:15	15:50	95
2/25/2004	891	FALSE	75	0	8:04	10:18	134
2/25/2004	891	FALSE	76	0	10:18	12:26	128
2/25/2004	891	FALSE	76	0	12:26	14:02	96

The FinishTime and TimePerOrder columns were correct. The correct LogoutTimes were drawn from tblLog. I didn't seem to have any problem.

 

[Noreene Patrick]

Jon K

Yours looked great, but as soon as I imported my original table tbllog to the db I unzipped from you, I still got that crazy number when I logged someone out in tbllog....Do you have any suggestion? I realize yours works fine, but what could make my number so crazy?

I am attaching a .txt file showing the crazy number I get when I log someone out in tblLog....Look at pickerid 209

Thanks, Noreene

 

[Jon K]

I was able to reproduce the number -54780384.

StartTime		FinishTime	Difference in minutes
2/25/2004 9:54:00 AM	11:30:00 AM	[b]-54780384[/b]

You must have put 11:30 in the LogoutTime field in tblLog, but leaving out the date.

You should have put 2/25/2004 11:30 in that field, just like your Time field in tblOrderWindow.

 

[Noreene Patrick]

Hello, Jon K

I know you will be glad to hear this..IT IS WORKING!!!!!! I had to make a query from tbllog with a field that added timeloggedout + date and so far that seems to work...I never was able to format the field in the table to make it work...dont know why...

But, many many many thanks to you for your help.

Noreene

 

[Jon K]

I am glad that it finally works for you.

I never was able to format the field in the table to make it work...dont know why...

Most likely you have an input mask in the field that allows you to enter only the time.

Without specifying the date, Access would default to 12/30/1899. That was why it showed a difference of -54780384 minutes.

I had to make a query from tbllog with a field that added timeloggedout + date and so far that seems to work

In a previous post you mentioned the need to capture time after 12 midnight. Using TimeLoggedOut + Date() won't return the correct logout date if a picker happened to log out after midnight. You need to enter the date as well in the TimeLoggedOut field.


If some pickers do log out after midnight, or you just want to run the query retrospectively, you can change the query into a parameter one like this:-

SELECT [tblOrderWindow].[Date], [tblOrderWindow].[PickerID], [tblOrderWindow].[Wave], [tblOrderWindow].[RegularMinutes], [tblOrderWindow].[WaveMinutes], [tblOrderWindow].[Time] AS StartTime,
IIf(IsNull(Dmin("Time","tblorderwindow","[Date]=#" & [Enter a date] & "# and [PickerID]='" & [PickerID] & "' and format([Time],'yyyymmddhhnnss')>" & format([Time],'yyyymmddhhnnss'))),DLookup("LogoutTime","tblLog","[Date]=#" & [Enter a date] & "# and [PickerID]='" & [PickerID] & "'"),CDate(Dmin("Time","tblorderwindow","[Date]=#" & [Enter a date] & "# and [PickerID]='" & [PickerID] & "' and Format([Time],'yyyymmddhhnnss')>" & format([Time],'yyyymmddhhnnss')))) AS FinishTime,
DateDiff("n",[starttime],[finishtime]) AS TimePerOrder
FROM tblOrderWindow
WHERE ((([tblOrderWindow].[Date])=[Enter a date]))
ORDER BY [tblOrderWindow].[PickerID], [tblOrderWindow].[Time];

 

[Noreene Patrick]

Jon K

Just one more question concerning the query for calculating time between rows...

If a picker logs in say at 4:30, logs out at 9:30 then logs back into function at 10:15 and logs out at 14:00....how do I tell the query to pick up the correct logout times in the blanks...right now it pulls in the first logout even if logout time was 9:30 and starttime was 10:00...

That was something I didnt think about before....but, if you are tired of answering my many questions...then let me say, you have been such a tremendous help already, that I thank you for your time youve given.

Thanks, Noreene

 

[Jon K]

Since the query finds other start times after 9:30 in the table, it has no idea the picker has already logged out once at 9:30 and just continues pulling start times from the table until there are no more for that picker. Then it goes to tblLog and pulls the first logout time for the picker.

If pickers log out more than once in a day, I don't think you can do it with a query. You can do it only with VBA, as it is more flexible.

 

[Noreene Patrick]

Thank You, Jon K

I will work with it this way, It will need some babying but I am willing to do that for now...it isnt something that really makes a big problem...

Many Thanks, again!!!!!

Noreene