Comparing records in a table

[ChrisGow]

I have been searching on here and have not found an answer, could be due to me not seeing it or its not here. I have a multi part question.

Anyways, here is what I have. I am getting data from a time clock, which dumps the data into an access database. It dumps the data as EmployeeNumber (Number), DateTime(text). I use the format command and make a new table to give me EmployeeNumber(Number), Date(text), Time(text).

Question 1:How can I convert these to date and time fields instead of text fields?

After I do that I need to be able to compare the records within the table, such as: I need to check to see if the date is the same between one record and the one above it (assume that I have sorted this correctly) if the date is the same then I need to subtract the first record from the second record to give me the number of hours between the two records.

Question 2: How would I go about comparing those records in the same table?

Thanks in advance to those who can help. Even ways not to do this would be good.

 

[ChrisGow]

OK quetion 1 I have figured out, just need help on question 2

 

[The_Doc_Man]

Ah, you have suddenly stumbled across a basic concept of SQL that is masked by the simplicity of the process.

1. SQL used to mean SEQUENTIAL Query Language. (Some places use other "S" words like SIMPLE.) But - hint - it is still a sequential, single-record process. Compare? Not possible through any simple SQL because SQL is inherently one record at a time.

2. Having said that, you can still "cheat" a bit. In VBA, it is possible to open a recordset and "remember" some fields in VBA variables, then when you step to the NEXT record, compare the fields in the current record to the stuff you stored previously. See, a recordset is just like a file. (If it is a linked table, it probably IS a file.) Access .MDB files are just containers for disorganized datasets.

3. Why disorganized? Because unless you specify record order via a query, you have a limited guarantee on the order of record appearance. If you are trying to look at all employee records grouped by employee, unless the prime key of the table IS employee ID, you wouldn't get your desired grouping.

4. So if you are going to do this in VBA, remember to work through a query that orders your recordset in a way that is conducive to the task at hand. Such as ordering by employee number first, time field second?

 

[ChrisGow]

Ahh, I figured it would be a recordset that I would have to use, the only problem with that is that I have no idea how to use one as I never have. I have seen a couple of examples however i seem to get confused by all of them. Would you be able to step me through one?

 

[ChrisGow]

I had found a good thread on here that explains recordsets and all of the functions for it, unfortunatly I was pulled off the comp and lost the thread. I am now unable to find it again.

Anyone out there know what thread Im talking about or able to explain recordsets?

 

[The_Doc_Man]

You should be able to figure out recordset operations from the Help files with their examples.

Here is how you do it...

Imagine how you would do this with a file. Pre-sort file to correct order. Read a record, store data. Read another record, compare to stored data, take action, store data. Repeat cycle until done.

OK, now take "file" and change that to "recordset" - viola, you are done.