Hi all, I hope you guys can help me out. I have an attendance db. Each day an employee is not at work we create a record. My question is if an employee is out for three consecutive days, how can I combine all three records in to one with the start day being the first day they were out and the end date being the last day?

Thanks.

Hmmm. Why bother as you already have the data you need. Also, what happens if a person is out 4 days? What if they are out only 2 days, do you not 'combine' those records?

Just wondering...

Jack

Thanks for responding, I shold have been clearer. Sorry, I only mentioned three as an example. The data is actualling being imported from a source that logs each day individually. I would like to combined all consecutive days into one. Any ideas you have will be greatly appreciated.

Hopefully there is a field in your data that changes so you know when you move from one set of data to another. Anyway, this is going to require code to loop through the recordset, get the first date, move forward until the data changes (EmploeyeedID?) then get the date for that record and add it to the table in question. This could get complicated if there are only dates and they span a weekend or holiday as they will not be consecutive...

The only way I see to do this is to write some code...

Does that help?

Jack

Yes, it helps a little. Any ideas on the coding? Each record does have it's unique id and an employee id.