asked on

Get availability count from sequenced data (Access DB)

I have a VB6 application using an Access database. These are the two tables which are pertinent to this question:

Slot
----------------------
Slot_Seq | Slot_ID
1 | 3
2 | 23
3 | 6
4 | 4
5 | 17
6 | 9
7 | 8
...

Booking
-----------------------------------------
ID | From_Date | To_Date | Slot_ID
1 | 02/20/09 | 02/28/09 | 9
2 | 02/26/09 | 03/02/09 | 23
...

The tables are related (Slot [one-to-many] Booking) on the Slot_ID field.

Slots can be arranged in various ways within the application, hence the Slot_Seq field.

What I need is a query which, when given a starting Slot and From & To dates, will return the number of available slots (completely vacant) in the currently displayed sequence.

Notes:
It is safe to assume that the starting slot is always going to be vacant for the specified dates.
Dates are in the US format "mm/dd/yy"

Some examples based on the above data:

Slot 6 from 02/19/09 to 02/26/09 = 3 available (6,4,17)
Slot 3 from 02/22/09 to 02/28/09 = 1 available (3)
Slot 23 from 02/19/09 to 02/25/09 = 4 available (23,6,4,17)

All I want from the query is the count (i.e. 3, 1 or 4 respectively). Ideally I would like to accomplish this in a single query, but if it has to be done in two or more stages, then so be it.

TIA

-Ant

Milleniumaire

I don't understand your requirement, can you give more information please.

You say "Slot 6 from 02/19/09 to 02/26/09 = 3 available (6,4,17)"

What do the numbers in the brackets represent (I assume they are slot numbers). I can't see a relationship between Slot 6 and the numbers 6,4,17. Can you please explain.

Antagony1960

ASKER

Yes, the numbers in brackets are the Slot_IDs.

Taking slot_ID 6 as a starting point: the next slot_ID in the sequence is 4, which doesn't have any bookings spanning the specified dates. Ditto for the next one in the sequence (Slot_ID 17). But the next one in the sequence (Slot_ID 9) does have a booking (ID 1) on it, so that marks the end of a block of availability. Therefore, a block of three adjacent slots (6, 4 & 17) is available for booking.

Antagony1960

ASKER

Perhaps I haven't explained this very well; I will try to explain it more clearly:

Within the application, slots can be ordered by various complex criteria which are not always readily translatable to an Order By clause. So, when the user picks a certain criteria the slot_Seq field is rewritten so that the displayed data can be ordered by this sequence field. This means that while the slot_Seq field will always match the displayed rows, the slot_IDs may be in any old order.

The attached picture shows how the data in my first post would be displayed to the user. The blue box indicates the user's current selection. What I need is a query to tell me how many of the slots below (and including) the selection are available for booking in one block. In the picture, it is clearly Three because the fourth slot in the current view already has a booking on it.

I really need this operation to be quick so I was hoping to avoid opening a full RecordSet and looping through it.

-Ant
slots-sample.jpg

Antagony1960

ASKER

I've upped the points to the maximum.

I thought this would be a relatively trivial problem for the SQL experts here.

Any takers?

Antagony1960

ASKER

Thanks ModernMatt.

-Ant

ASKER CERTIFIED SOLUTION

irudyk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Antagony1960

ASKER

Thanks for the response irudyk.

A problem I can see straight away with your suggested query is that you're basing it on knowing the slot_Seq field, but I only know the Slot_ID field at that point. EG: the cursor is on slot 6 (which is Slot_ID 6) in the picture above.

-Ant

irudyk

Okay, well the SQL statement provided left joins the Slot_Id (the value you say you have) between the Bookings and Slot tables in order to obtain it's related Slot_Seq id. So, by your table definitions, if you know the Slot_Id you would know it's Slot_Seq id wouldn't you (please correct me if my logic here is incorrect)? Also, did you attempt to run the SQL to see if you got the correct results (I ran it here using the above data you outlined and seemed to get the correct count results)?

Antagony1960

ASKER

@irudyk:

<<did you attempt to run the SQL to see if you got the correct results>>
I hadn't at the time of my last comment, but I have now and yes, it does appear to be giving me the correct results if I work out what the sequence number is and enter that.

As for knowing the selected row's sequence number beforehand: well it isn't something I've previously had any cause to keep track of as it has only ever been used as a simple means to set the display order in the application. I guess I could ascertain the sequence number from the selected row and the scroll bar value (there may be many hundreds of rows to choose from) but that feels like a less reliable way than doing it all in the SQL statement. Plus, I'd prefer not to have to edit the existing form unless I really have to. Is it not possible to get the count in a single SQL statement, given only the dates and the Slot ID?

irudyk

From the data you've provided, I'm not sure of how to get the information you are looking for without knowing what the slot_seq is. I mean, you are looking for a count from the row you are on (e.g. Slot 6) up to the last applicable blank row where the dates selected do not intersect (e.g. Slot 17). Is the ordering of these rows not based upon the slot_seq? If so, I 'm not sure why you would have to "enter" that value (it would be part of the relationship between the row tables which would provided you with that value).
I really don't see another way...how else is one to know that Slot 8 comes after Slot 17 if not to rely on the slot_seq value? How would one know to ignore Slot 8 if Slot 9 has an intersecting date if not to rely on the slot_seq value?
Since you have all of the necessary data at your disposal, maybe you could offer up a suggestion as to how you think this could be accomplished without using the slot_seq value. Or, maybe you could outline how your form is set up such that it knows how to display the Slot numbers in the sequence that they are in without the use of the slot_seq number.

Antagony1960

ASKER

The displayed rows are indeed ordered by the slot_seq field, but the code to create the display opens a recordset, draws the display and then closes the recordset again. It's not a datagrid or anything like that. So you see there is no existing cross-reference which gives me the slot_seq number for a selected row. Working out what the slot_seq is would not be too difficult if that's the only way to do it--I could save it in an array or even use another temporary recordset to get it from the slot table if needs be--but I was rather hoping that it wouldn't be necessary. I just thought it would be possible to get the availability in a single SQL statement, given only the slot_ID and selection dates as parameters, but if you're telling me that it can't be done, then I'll accept that I have to get the sequence number for the query to work.

irudyk

Okay, I see what you are saying about how you populate the form and would agree that storing the slot_seq value in some way would be the way to go (maybe store the value as part of the slot object's "tag" property and then reference the value from there). In the end you would be able to get the information you want using a single SQL statement (albeit a rather lengthy statement).
Overall, I guess I'm saying that you can't get the information you are looking for (through the use of a query) by using the slot_id value since that value doesn't provide you with the information needed to retrieve the count value. The count is dependent upon how the slots are sequenced/ordered on the form which is based on the slot_seq value (which is also the way you originally populate the form). So, without that information I can't see how you'd ever be able to get an accurate count.
Of course, maybe someone else with a fresher set of eyes might be able to suggest something else that would work. Good luck with it! :-)

Antagony1960

ASKER

<<maybe store the value as part of the slot object's "tag" property and then reference the value from there>>
I can't use the Tag property because it's already being used extensively, but I can use one of the other numeric properties which is not being used, such as the WhatsThisHelpID.

There is another slight problem with your query, however, irudyk--and I'm not sure it will be an easy one to fix (within the query). Say there are 20 slots and slot (seq) 6 is selected but there are no bookings below between the selected dates: ideally, it should return 15, but it's actually returning 0 (zero). Again, if this is difficult or impractical to accomplish within the query it won't be too difficult to work it out afterwards if and when the query returns 0.

Antagony1960

ASKER

Okay irudyk, I think I've worked out how to get the availability without knowing the Slot_Seq value. here's what I did:

I replaced the three instances of the form field [Slot_Seq] in your query with this (now using [Slot_ID] as the form field]):

...(SELECT Slot_Seq FROM Slot WHERE Slot_ID = " & [Slot_ID] & ")...

I've tested it in as many ways as I can think of and it seems to be holding up. Other than increasing the length of an already long query string, can you see any problems that this might cause? I'm not too fussed about the other problem as I can work that out very easily.

Thanks for all your help. It wasn't 100% what I asked for but it put me on the right track and that's good enough for me.

-Ant

Antagony1960

ASKER

Thanks for all your help.

irudyk

Hmm, well the issue with the zero count result could be fixed by using the following SQL statement. You would need to replace the values with references to the applicable fields from the form.

'Sample SQL
SELECT TOP 1
IIf((SELECT First(Slot.Slot_seq) AS Slot_seq 
FROM Slot LEFT JOIN Booking ON Slot.Slot_Id = Booking.Slot_ID
WHERE (((Slot.Slot_seq)>=3) AND ((Booking.From_Date) Between #2/19/2008# And #2/25/2009#)) OR (((Slot.Slot_seq)>=3) AND ((Booking.To_Date) Between #2/19/2008# And #2/25/2009#))) Is Null,
(SELECT Count(Slot.Slot_Id) AS Slot_Id
FROM Slot LEFT JOIN Booking ON Slot.Slot_Id = Booking.Slot_ID
WHERE (((Slot.Slot_seq)>=3))),
(SELECT Count(Slot.Slot_Id) AS Slot_Id
FROM Slot LEFT JOIN Booking ON Slot.Slot_Id=Booking.Slot_ID
WHERE (((Slot.Slot_seq)>=3 And (Slot.Slot_seq)<(SELECT First(Slot.Slot_seq) AS Slot_seq
FROM Slot LEFT JOIN Booking ON Slot.Slot_Id = Booking.Slot_ID
WHERE (((Slot.Slot_seq)>=3) AND ((Booking.From_Date) Between #2/19/2008# And #2/25/2009#)) OR (((Slot.Slot_seq)>=3) AND ((Booking.To_Date) Between #2/19/2008# And #2/25/2009#))))))) AS SlotCount
FROM Slot;
 
'Revised SQL using form field names
SELECT TOP 1
IIf((SELECT First(Slot.Slot_seq) AS Slot_seq 
FROM Slot LEFT JOIN Booking ON Slot.Slot_Id = Booking.Slot_ID
WHERE (((Slot.Slot_seq)>=" & [S1ot_seq] & ") AND ((Booking.From_Date) Between #" & [From_Date] & "# And #" & [To_Date] & "#)) OR (((Slot.Slot_seq)>=" & [S1ot_seq] & ") AND ((Booking.To_Date) Between #" & [From_Date] & "# And #" & [To_Date] & "#))) Is Null,
(SELECT Count(Slot.Slot_Id) AS Slot_Id
FROM Slot LEFT JOIN Booking ON Slot.Slot_Id = Booking.Slot_ID
WHERE (((Slot.Slot_seq)>=" & [S1ot_seq] & "))),
(SELECT Count(Slot.Slot_Id) AS Slot_Id
FROM Slot LEFT JOIN Booking ON Slot.Slot_Id=Booking.Slot_ID
WHERE (((Slot.Slot_seq)>=" & [S1ot_seq] & " And (Slot.Slot_seq)<(SELECT First(Slot.Slot_seq) AS Slot_seq
FROM Slot LEFT JOIN Booking ON Slot.Slot_Id = Booking.Slot_ID
WHERE (((Slot.Slot_seq)>=" & [S1ot_seq] & ") AND ((Booking.From_Date) Between #" & [From_Date] & "# And #" & [To_Date] & "#)) OR (((Slot.Slot_seq)>=" & [S1ot_seq] & ") AND ((Booking.To_Date) Between #" & [From_Date] & "# And #" & [To_Date] & "#))))))) AS SlotCount
FROM Slot;

Open in new window

irudyk

Yep, using a select query to retrieve the slot sequence from the slot id will work. In essence you are achieving the same the same thing as I outlined (i.e. you needed to retrieve and use the slot sequence to get your count values).

Antagony1960

ASKER

WOW! Well that's it then... everything in one query just like I asked for. Even if it is longer than the Suez canal! :-D

Thanks again for all your help there irudyk.

irudyk

Yep, it is a long query and with the use of your additional SELECT query (i.e. to get the slot sequence), it sure didn't get any shorter :-) I'm glad it worked out for you!