Start and End of date ranges

[BrianOg]

Given a range of dates held in a field in a table or query, e.g.:
10/1/04,11/1/04,12/1/04, 23/1/04, 3/2/04,4,2,04, 8,2,04
How can I write a query (in Access SQL) to generate:

fldStart fldEnd
10/1/04 12/1/04
23/1/04 23/1/04
3/2/04 4/2/04
8/2/04 8/2/04

Would appreciate your help very much

Brian

 

[lespaul]

How do you know what's the start and what's the end?

How are the dates stored in the table? Is it a single field or do you have two fields?

With the information you have provided, it is impossible to help. Please provide additional information about your tables, the fields in the tables and how they relate to get the best answer.

 

[BrianOg]

Sorry the question was not clear.
The dates are produced by a query and are in one field.
The start date is at the start of a sequence of consecutive dates where the difference between dates is one day and the end date is at the end of this sequence.

So: given the dates:
10/1/04,11/1/04,12/1/04, 23/1/04, 3/2/04,4/2/04, 8/2,04

The first three have a one-day difference. Start: 10/1/04, End: 12/1/04
Then there is a 11 day gap
Next comes a date flanked by gaps of more than one day, so this is the start and end of this set.
The next two have a one-day diffence so Start: 3/2/04, End: 4/2/04
The last is a single date: Start: 8/2/04, End: 8/2/04

I want to write a query whose source is the query which produces this date sequence. The new query should output start date in one field and the end date in a second field for each sequence.

 

[BNPMike]

Relational databases sort of assume you are using relations. What you've got there isn't. If that is a record it is not in First Normal Form. As a result SQL struggles to deal with it. It's like your car assumes you are going to use roads. It's not so good on railway tracks.

Can't you get the data into a more Access-friendly format before bringing it into Access?

 

[lespaul]

What does the table look like that you get this single field query result set? Perhaps there's a way to get the information you need before you get to this point.

Leslie

 

[BrianOg]

Hi Leslie and Mike(?),
Thanks for your replies.
I have a vacancies table (tblVacancies) in which each record has a fldID Autonumber field. It also has start and end date fields (fldStart and fldEnd)
I have a bookings table (tblBookings) which has a fldVacID field relating to fldId in tblVacancies. This also has fldStart and fldEnd fields.
There can be numerous bookings for each vacancy, each with a start and end date. None of these overlap.
These bookings fill time gaps in the vacancies table, leaving vacant gaps.
I need to determine the start and end of these gaps and display these in columns of a combobox.
I assume these tables are normalised – no duplication of data, unique id for individual records.
I value your help.

 

[BNPMike]

It looks like you have a one-to-many relationship here. Vacancy seems to be a period when bookings can be made. And booking is a contiguous set of days within a Vacancy period. Now I'll just say a potential flaw with this model is the booking may overlap more than one vacancy, either now or in the future.

You'd be better having a separate record for each booking rather than trying to squeeze them into one record. The reason -as I said above - is SQL assumes 1NF and that means 'No multiple value fields'. Relate each booking to the appropriate vacancy. If necessary you can then have a 3 - entitiy model where bookings can span more than one vacancy.

 

[BrianOg]

Hi Mike,
Quite correct. It is a one to many relationship. I do have a separate record for each booking but only one record for each vacancy, e.g.:

Vacancy: VacID=983 Start=1/1/04 End=23/4/04

Booking 1: ID = 2345 VacID = 983 Start=12/1/04 End =17/1/04
Booking2: ID=6744 VacID=983 Start=31/1/04 End=7/2/04
Booking3: ID=1111 VacID=983 Start=12/2/04 End=12/4/04
Etc, etc.

I intended to write coding in button handlers to prevent a booking being made outside the vacancy period and that the booking dates are not outside the vacancy period.

I thought, if I can extract the vacant ranges in the vacancy period, I could put these as columns in a combo box and when the user selects, say 1/1/04 to 11/1/04 (each in a separate column derived from the query in question) the coding would then display by changing the RecordSource of two other combos the actual dates in the range which would avoid wrong entries.
If I were to do this, I would need to extract, presumably with a query:
From: 1/1/04 To: 11/1/04
From: 18/1/04 To: 30/1/04,
Etc
For the first combo.
I can work out how to extract the actual dates in the ranges using another query.
Hope I have made it clear. Of course, you may well tell me that there is a much easier more reliable way of doing it!!!!
Very much appreciate the help you are being to me. I work for a charity (being retired) and do not charge them for my services so it is charitable work in a really good cause – helping the elderly and incapacitated.
Regards
Brian

 

[raskew]

Allen Browne provides an excellent article on how to identify overlapping conditions within a table.

http://members.iinet.net.au/~allenbrowne/appevent.html

 

[BrianOg]

Have already seen Allen Browne's article.
Don't think it is appropriate to my problem (unless I'm dumb - I often am!!!).
What I really want to do is "subtract" the date ranges of the bookings from the date range of the vacancy, leaving new ranges for the remaining days. Assume this could be done with a self-join but can't work out how to do it.