Ho Ho Ho! I've been told to re-post this question in this section. It originally was in the Office software section but anyways.. I am having trouble with getting a certain calculation. Presently, I have a table with the following columns: Time, TagNo, and Status. Time is in typical hh:mm:ss format (which is a text field but that isn't the issue because I can use other functions to swap it back and forth inbetween text & time type). TagNo has various whole numbers (ie., 1,2,3,4,5...). Status is either 0 (zero) or 1. Here's where I come to a problem.. How does one calculate the time difference between similar records. Example 1.. (Record 1) 8:42:02 3 0 (Record 2) 8:42:10 1 0 (Record 3) 8:43:25 2 0 (Record 4) 9:00:10 1 1 (Record 5) 9:01:20 1 0 (Record 6) 9:03:20 2 1 (Record 7) 9:04:24 3 1 (Record 8) 9:10:24 1 1 With the above data, the matching records are: 1 & 7, 2 & 4, 3 & 6, 5 & 8. How can I determine the time inbetween each matching record? (I can also easily query to have all TagNo's seperate tables if that helps.) The desired calculations are easy to do in your head.. but I can't figure it out in Access 2000. I've been trying to work it out via a macro, query, or Visual Basic but I remain stuck. Any help?

I think your first mistake is saving the Times as a text field. If you were to make it a Time/Date field type it would be stored as a floating point number and calculations would be a doddle. Access and VB is full of functions to do Date/time calculations, but only on numbers, not text. Look up DateDiff Stuart Stuart

It looks like you have a classic time card problem. Sort by Tag No (assending) and Status (decending). Then subtract the time from even records from the time of the previous odd record. Assuming Status 0 is start time and Status 1 is end time. A better data structure would probably be Tag No Start Time End Time (This is assuming you are entering directly into the data base.) If you are getting you data as a sequential stream (like you example) you could run a data transformation run to arrange the data. How are you handling missing data (status 0 with no status 1 or status 1 with no status 0)?

thanks for the tips. however.. both still leave me with the issue. stuart, i would have stored the time with the appropriate date/time format if i could. however, that data is automatically generated so i am trying to manipulate the data. **however**, i know i am able to convert the text version of the time to the correct format. wizard-fred, the idea to subtract even records from odd records has crossed my mind... until i noticed i have missing data (as your last paragraph points out). i am not sure of how i will deal with the missing data issue yet.. :( what do you mean by "a data transformation run to arrange the data"? *perks interest*

First a question. Is the TAG ID a unique number that does not rollover at 999999 or someother number? Data transformation. I assume that the data is being logged into a text file. I would run a program to process or preprocess the data. It would (1) check the data records for completeness, [write out bad records to an error file] (2) sort remaining records, (3) process the sorted record pairs [write complete pairs to final data file, write records without pair to error file] Other considerations - unmatched records that will be completed in the next batch. Actually the program could calculate the time interval when it finds a matching pair. Some background - I started using computers before the spreadsheet. We had to write our own tools to process data. We had Y2K solutions in 1982.

the TagId never goes over 20. And the data is being automatically logged to an access table in that layout. i'll have to review the data again and see what i can do about removing missing data. if this was a time-card project, having the system realise they are punching out twice in a row would mean the system lost when an employee punched in thus causing financial mistakes. however, this is not for a time-card project.. but recording when equipment/machines are running & down. not as vital as a time card system... but still strikes my curiosity. that would be the next step, correct? figuring out what to do with the missing entries?