Extract period of a range of dates

[THANOS]

Hi!

Now here is my issue that I hope someone could assist me on solving this. I have a table that presents the days of absence of an employee on a business trip. It goes like this:
NAMEID.........DATE
3..................1/8/2008
3..................2/8/2008
3...................3/8/2008
3..................15/8/2008
3..................16/8/2008
4...................1/7/2008
4...................2/7/2008
12.................15/8/2008
12.................16/8/2008
I was wondering if it is possible to create a table query to show each period of absence for each employee as below:

NAMEID.........STARTDATE.........ENDDATE
3....................1/8/2008...........3/8/2008
3....................15/8/2008.........16/8/2008
etc
also to add a check box field at the end named "TaskCompleted" were to check if a task is finished or not.

Any help would very much appreciated.
Thank you

 

[Uncle Gizmo]

What about adding an extra field to identify the trip?

 

[THANOS]

I'm not sure what you mean. If I understand you suggest to add a new field in my current table. But this is not the issue. If it was that simple I could do it without asking. The point is that I don' t want to change the original table or the way I input the data in it.

Any idea ?

 

[Uncle Gizmo]

>>>If it was that simple I could do it without asking<<<

I have no idea of your level of expertise in access, so I have to start somewhere in composing a suitable answer don't I.

One alternative is to write a function that adds a trip identifier into a calculated column in a query.

Then use a cross tab query based on the previous query to transpose your data.

 

[georgedwilkinson]

You'll most likely need to order a recordset by the date and iterate the recordset one row at a time (in VBA). Every time the date skips more than 1 day, you capture the previous date as your "to date", write the "result" to a temporary table, set the newest date to your "from date", reset your "to date", and keep going until you've read all the records. You're gonna have to get your hands dirty and your programming/logic skills need to be up to date.

 

[THANOS]

Guys, thank you both for your quick replies. Though I wouldn' t say that I am totaly new at access I am afraid that my level is not much higher than a beginner+. Furthermore this date thing is not my strong point. I would really appreciate If you could provide me with a little example so I can proceed with my project.

Thanks again.

 

[Uncle Gizmo]

>>> I would really appreciate If you could provide me with a little example so I can proceed with my project<<<

I'm afraid I don't have the time at the moment, I'm late for work already! However someone else may oblige, other than that you will need to know how to construct a function.

 

[khawar]

Download the attached sample and check sampleQuery

 

[THANOS]

Download the attached sample and check sampleQuery

Fantastic. This was exactly what I was looking for. Thank you very much.

I watch this forum for more than a year and I got valuable ideas and knowledge.

You are doing a great job and you are very helpful. Thanks once again.

RESPECT !!

 

[THANOS]

oops me again! I checked the given query with my data and I realized that the results I got where a bit strange.

I give you an example in the attached file where you can see that for the nameID 3 instead of giving me back the ranges
4/8 to 8/8, 11/8 to 14/8 etc I got the strange result of 4/8 to 22/8 and 18/8 to 21/8 and so on.

Any ideas what might gone wrong ?

 

[khawar]

My mistake check it now

 

[THANOS]

works fine now! Thank you!!