Match and Consolidate

[Yap]

Original table
Rec, T, Date In, Time In, Date Out, Time Out


-----------------------------------------------------
8, 110, I, 12/3/2010, 7:36, -, -
9, 110, O, -, -, 12/3/2010 ,11:50
10, 110, I, 12/3/2010, 12:57, -, -
11, 110, O, -, - ,12/3/2010, 16:31

Same table consolidated
Rec, T, Date In ,Time In, Date Out, Time Out
-----------------------------------------------------
8, 110, C ,12/3/2010, 7:36, 12/3/2010, 11:50
10, 110, C, 12/3/2010, 12:57, 12/3/2010, 16:31

- = null I've put , to make it look better here.
Rec is just the record number

How can I accomlish this with a button on a form in MS Access?
In a software I have (made maybe in Fox Pro) they have a menu option to do this. I want to do this too but in access.

Yap

[pkstormy]

I'm confused. Could you explain a bit differently?

[SteveF]

I'd think really hard about redesigning your table instead. Consider something like this:

RowID Long Integer or AutoNumber
Log ID Long Integer
Rec Long Integer
EntryType Long Integer or Text
EntryTime DateTime

The data you supplied would then look like this:
1 1 110 "Date In" 12/3/2010
2 1 110 "Time In" 7:36
3 1 110 "Date Out" 12/3/2010
4 1 110 "Time Out" 11:50
5 2 110 "Date In" 12/3/2010
6 2 110 "Time In" 12:57
7 2 110 "Date Out" 12/3/2010
8 2 110 "Time Out" 16:31

You can then use queries to re-format this data for display in a form or report, handling any "nulls" quite easily. In the long run, this will be easier to maintain and enhance than your original concept.

[Yap]

The "original" table is what the time and attendance device collects. Each record (row) is either IN or OUT. In the commercial software there's a match option in utilities that conoslidate the data per day and per cust so now you have one row with both IN and OUT. In the proccess it repaces the I and O to C (completed or consilidated). All this is done to the same table.

The commercial software uses MS Access database but the app front I don't know but for sure is not access.