Finding sequences in a query? 1

[bartus991]

I'm am making a small programm for seat reservations. But I have a problem to find the sequence in the available seats. I will explain it with the following sample:

Row Seat Total Seats In Sequence Sequence number Available
1 1 3 1 3
1 2 3 2 2
1 3 3 3 1
1 6 4 1 4
1 7 4 2 3
1 8 4 3 2
1 9 4 4 1
2 1 3 1 3
2 2 3 2 2
2 3 3 3 1
2 8 2 1 2
2 9 2 2 1

If have tried to do it with Dcount but this function is very slow. The output of available seats is the row that I use for the rest of the database.

Please let me know if you have a better solution to get the availeble seats next to each other as an output.

For example if I need 3 seats the system have to give me to following options:

Row Seat Total Seats In Sequence Sequence number Available
1 1 3 1 3
1 6 4 1 4
1 7 4 2 3
2 1 3 1 3

Hope that you can help me, maybe Ecount works better but I can't figure out I can make it work for my query.

Best regards
Bart

 

[Andrzejek]

Trying to make sense out of what you posted....

If I understand you correct, you have a Table with Row, Seat, Total Seats In Sequence, Sequence number, and Available columns.

Can you just do the Select query to get your output:

[blue]
SELECT * FROM YourTable
WHERE “Total Seats In Sequence” >= Available
ORDER BY Row, Seat[/blue]

Am I even close.....?

Have fun.

---- Andy

 

[MichaelRed]

I think you need to start back at the structure.

A basic premise of modern / relational db is to NOT store that which can be calculated, so having the "Total Seats In Sequence" is probably a poor choice in persistant data.

I think I would probably look at using an array where each ROW of the array element represented a row of seats, and each element would represent an individual seat. Of course, seating charts seldom have strictly uniform structure, so you would need to be able to indicate the individual seats which are never available ...

The array of seats also needs a dimension for the various events, so this is another issue which needs to be addressed, but it needs a resoloution regardless of the mechanics of the seat groups.

Given the array, is should be relatively easy to take the number of required adjacent seats and XOR a pattern with each row until a match is found ...

If you need three adjacent available seats, create a (temporay) arraay of three values (-1, -1, -1; assuming a -1 indicates an available seat) XOR this agiinst a "row" from the first seat to the last one (-2, so it is not comparing to non-existant seats).

when the XOR returns all zeros, you have found the space.



MichaelRed

 

[bartus991]

Dear MichaelRed,

I'm not a programmer, I have experience with Microsoft Acces, but I'm still learning. So actually I dont know how to do what you are meaning. I know a aary, but I always avoid as I can't figure out how it exactly works.

Maybe you can help me, I have a small database on the, please see attachment. So maybe you can look into it to create a new structure. As the lagest room contains 26 rows and almost 500 seats it have to be a good working and fast query.

Thanks in advance
Bart

 

[MichaelRed]

I am, currently, somewhat busy but may be able to look at it briefly next week.



MichaelRed

 

[MajP]

Test.

 

[MajP]

I have an answer but the web page keeps crashing when I try to submit it so I will try it in two parts.

Part 1:

I would think a pure sql solution would be kind of hard, so I did this simply with some code and a few queries. And like michael said you do not need to record "Total Seats in Sequence", "Sequence Number" and "Seats Avaialble" we will do that dynamically based on remaining seats.

1. Build an output table "tblSequence"
tblSequence
rowNumber
seatNumber
sequenceID (a unique identifier for each sequence"
sequenceNumber (the number that the seat is in the sequence)

2. Write this code to build your output table based on your
your qry_Available_seats

Public Sub createSequenceTable()
  Dim rsAvailable As DAO.Recordset
  Dim rsSequence As DAO.Recordset
  Dim rsSequenceCount As DAO.Recordset
  Dim strSql As String
  Dim sequenceID As Integer
  Dim rowNumber As Integer
  Dim seatNumber As Integer
  Dim tempRowNumber As Integer
  Dim tempSeatNumber As Integer
  Dim tempSequenceID As Integer
  Dim sequenceNumber As Integer
  
  Set rsSequence = CurrentDb.OpenRecordset("tblSequence", dbOpenDynaset)
  Set rsAvailable = CurrentDb.OpenRecordset("QRY_Available Seats")
  strSql = "Delete * from tblSequence"
  CurrentDb.Execute strSql
  Do While Not rsAvailable.EOF
    rsSequence.AddNew
    rowNumber = rsAvailable!row
    seatNumber = rsAvailable!seat
    rsSequence!rowNumber = rowNumber
    rsSequence!seatNumber = seatNumber
    If rsAvailable.BOF Then
      rsSequence!sequenceID = 1
      tempSequenceID = 1
      sequenceNumber = 1
    ElseIf rowNumber = tempRowNumber And (seatNumber = tempSeatNumber + 1) Then
      rsSequence!sequenceID = tempSequenceID
      sequenceNumber = sequenceNumber + 1
    Else
      rsSequence!sequenceID = tempSequenceID + 1
      tempSequenceID = tempSequenceID + 1
      sequenceNumber = 1
    End If
    rsSequence!sequenceNumber = sequenceNumber
    tempSeatNumber = seatNumber
    tempRowNumber = rowNumber
    rsSequence.Update
    rsAvailable.MoveNext
  Loop
  
  
End Sub

 

[MajP]

Part 2

This is your output. Notice each sequence has a unique identifier and each seat has a sequence number.

RowNumber seatNumber sequenceID	sequenceNumber
1	2	1	1
1	4	2	1
1	5	2	2
1	6	2	3
1	7	2	4
1	8	2	5
1	9	2	6
1	10	2	7
2	1	3	1
2	2	3	2
2	3	3	3
2	4	3	4
2	5	3	5
2	6	3	6
2	7	3	7
2	8	3	8
2	9	3	9
2	10	3	10

3. Now build a simple query on this table:

SELECT tblSequence.sequenceID, Count(tblSequence.sequenceID) AS CountOfsequenceID
FROM tblSequence
GROUP BY tblSequence.sequenceID;

 

[MajP]

Part 3
This creates output like

sequenceID	CountOfsequenceID
1	        1
2	        7
3	        10

now build another query using the total table

SELECT tblSequence.RowNumber, tblSequence.seatNumber, tblSequence.sequenceID, tblSequence.sequenceNumber, qrySequenceTotals.CountOfsequenceID, [countofsequenceID]-[sequencenumber]+1 AS Remaining
FROM tblSequence INNER JOIN qrySequenceTotals ON tblSequence.sequenceID = qrySequenceTotals.sequenceID;

This gives you something like

Row	seat	seqID	seqNumber CntOfseqID Remaining
1	2	1	1	1	1
1	4	2	1	7	7
1	5	2	2	7	6
1	6	2	3	7	5
1	7	2	4	7	4
1	8	2	5	7	3
1	9	2	6	7	2
1	10	2	7	7	1
2	1	3	1	10	10
2	2	3	2	10	9
2	3	3	3	10	8
2	4	3	4	10	7
2	5	3	5	10	6
2	6	3	6	10	5
2	7	3	7	10	4
2	8	3	8	10	3
2	9	3	9	10	2
2	10	3	10	10	1

 

[MajP]

Part 4

and a simple query returns your answer

SELECT qrySequenceCount.RowNumber, qrySequenceCount.seatNumber, qrySequenceCount.sequenceID, qrySequenceCount.sequenceNumber, qrySequenceCount.CountOfsequenceID, qrySequenceCount.Remaining
FROM qrySequenceCount
WHERE (((qrySequenceCount.Remaining)>=[Enter Amount of Desired Seats]));

 

[MajP]

Part 5

If I am looking for 4 seats the answer is

Row seat seqID seqNumber CntOfseqID Remaining
1 4 2 1 7 7
1 5 2 2 7 6
1 6 2 3 7 5
1 7 2 4 7 4
2 2 3 2 10 9
2 3 3 3 10 8
2 4 3 4 10 7
2 5 3 5 10 6
2 6 3 6 10 5
2 7 3 7 10 4
[/code]

So after reserving seats you would need to run the procedure before searching for seats. Even with thousands of seats the procedure would take a few seconds.

 

[MajP]

I am not smart enough to write a Sql solution to find the original "tblOutput", but if you can find a sql for that query than the other parts are just simple queries. I will post the question in the Access Sql forum and see if I can get an answer.

Basically the question is given a row number and a seat number, can I build a query that gives a unique identifier to each contiguous sequence, and give a number for the place in the sequenc.

Row seat sequenceID sequenceNumber
1    2         1    1
1    4         2    1
1    5         2    2
1    6         2    3
1    7         2    4
1    8         2    5
1    9         2    6
1    10        2    7
2    1         3    1
2    2         3    2
2    3         3    3
2    4         3    4
2    5         3    5
2    6         3    6
2    7         3    7
2    8         3    8
2    9         3    9
2    10        3    10

 

[MichaelRed]

You should thank MajP]/color]. I had in mind a graphical interface (e.g. a form with the seating plan of the events and associated info (room performance ...) using the array and conditional formatting, it should be possible & practical to highlight the available sequences, with some basic controls to switch between the options to identify the users needs.



MichaelRed

 

[MajP]

OK as I said I am not very good with Sql so this is kind of ugly, but I figured out a pure sql solution.

1. Build a query to find all seats with a seat directly before them.

SELECT A.SeatId, A.Row, A.Seat, B.SeatId, B.Row, B.Seat
FROM [QRY_Available Seats] AS A LEFT JOIN [QRY_Available Seats] AS B ON A.Row = B.Row
WHERE (((B.Seat)=[A].[Seat]-1));

2. Next build a query that finds all records missing from the query above. That is your start of each sequence

SELECT [QRY_Available Seats].SeatId, [QRY_Available Seats].Row, [QRY_Available Seats].Seat
FROM [QRY_Available Seats]
WHERE ((([QRY_Available Seats].SeatId) Not In (select A.SeatID from qrySeatsWithPriorSeat)))
ORDER BY [QRY_Available Seats].Row, [QRY_Available Seats].Seat;

This returns
SeatId Row Seat
1A02 1 2
1A04 1 4
1B01 2 1

3. Now this is the tough part. There are three unique sequences. I want to identify each with its starting point from the above query.

SELECT a.SeatId, a.Row, a.Seat, b.Row, Max(b.Seat) AS BeginSequence
FROM [QRY_Available Seats] AS a INNER JOIN qryStartOfSequence AS b ON a.Row = b.Row
WHERE (((b.Seat)<=[a].[seat]))
GROUP BY a.SeatId, a.Row, a.Seat, b.Row
ORDER BY a.Row, a.Seat;

4. The above query assigned each available seat to its starting sequence row and column, but I want to include the starting sequence seat ID.

SELECT qrySeatsAndSequence.SeatId, qrySeatsAndSequence.a.Row, qrySeatsAndSequence.Seat, qrySeatsAndSequence.b.Row, qrySeatsAndSequence.BeginSequence, [QRY_Available Seats].SeatId AS BeginSequenceID, qrySeatsAndSequence.seat-[beginSequence]+1 AS SequenceNumber
FROM qrySeatsAndSequence INNER JOIN [QRY_Available Seats] ON (qrySeatsAndSequence.b.Row = [QRY_Available Seats].Row) AND (qrySeatsAndSequence.BeginSequence = [QRY_Available Seats].Seat);

5. Now I need to get the amount of seats in each sequence

SELECT qrySeatsAndSequenceWithID.BeginSequenceID, Count(qrySeatsAndSequenceWithID.BeginSequenceID) AS CountOfBeginSequenceID
FROM qrySeatsAndSequenceWithID
GROUP BY qrySeatsAndSequenceWithID.BeginSequenceID;

6. Now I want to finish and produce a query that has the same information as the table I produced in code

SELECT qrySeatsAndSequenceWithID.SeatId, qrySeatsAndSequenceWithID.a.Row, qrySeatsAndSequenceWithID.Seat, qrySeatsAndSequenceWithID.BeginSequenceID AS SequenceID, qrySeatsAndSequenceWithID.SequenceNumber, qrySequenceTotals.CountOfBeginSequenceID AS NumberInSequence, [CountofbeginSequenceID]-[SequenceNumber]+1 AS Remaining
FROM qrySeatsAndSequenceWithID INNER JOIN qrySequenceTotals ON qrySeatsAndSequenceWithID.BeginSequenceID = qrySequenceTotals.BeginSequenceID;

So here is the data

SeatId	Row  Seat       SeqID	SeqNumber #InSequence Rem
1A02	1	2	1A02	1	1	1
1A04	1	4	1A04	1	7	7
1A05	1	5	1A04	2	7	6
1A06	1	6	1A04	3	7	5
1A07	1	7	1A04	4	7	4
1A08	1	8	1A04	5	7	3
1A09	1	9	1A04	6	7	2
1A10	1	10	1A04	7	7	1
1B01	2	1	1B01	1	10	10
1B02	2	2	1B01	2	10	9
1B03	2	3	1B01	3	10	8
1B04	2	4	1B01	4	10	7
1B05	2	5	1B01	5	10	6
1B06	2	6	1B01	6	10	5
1B07	2	7	1B01	7	10	4
1B08	2	8	1B01	8	10	3
1B09	2	9	1B01	9	10	2
1B10	2	10	1B01	10	10	1

And that is why I did it in code.

 

[MajP]

Here is the final solution

http://www.4shared.com/file/88095743/7314c7df/FindSequence.html

It took me several intermediate queries to do this, but qryFindSeats is the final answer.

 

[bartus991]

First of all I want to thank MajP for the efforts, It works really great now! I am now editing just a few thing to get the best available seat! It works great...

But after the first problem, you'll find the next. After the query I'm creating a form based on the query that displays all seats an their status. For this I want to design a graphic of a seat with diferrents colours. For example a yellow seat fo the available seats, grey fo a blokked seat en differtent colours for a booked seat with a graphic of someone sitting on it. See the url for a example.

Design the graphics is not the problem, displaying them on a form that's the trick. I try'd to do it with the principe of the basic switchboard in access. But If you click on the desired seat It have the book that seat and the next seats that are desired. The main problem is the graphical displaying of the seats. The system automaticley gives you seats oout of the seats available query, and you have to look it up in seatmap and there you can alter the seatchoice. But the seats you are booking will have a different colour the the already booked/reservered seats.

Difficult... I know... I'm already happy that the first part of finding the seats works terrific, but now i want to go further. The output qeury of available seats gives on the first row always the best available seatingrange and has a no single seat left 'function'...

 

[MajP]

The URL does not seem to work. Not sure exactly how you want this to work, I am curious on your thoughts about a switchboard. I can only think of building a form dynamically. Here might be a simple solution, not sure if it would fit your needs.
1) Make a query for a given performance of all seats. Basically it is the room configuration.
2) Now read through this table and get the min and max row numbers, and the min and max seats.
3) Build a new table called "tblSeatingGrid", which is a grid of all rows and all possible seats. Each record is a row, and each field is a seat number. Something like.

Rowid Seat1 Seat2 Seat3 Seat4
3
4
5
7
8

4) Now fill this table up. If the seat is taken put "Res", if not put the seat name "1A01" or something like that. If the seat does not exist in that row put something like "DNE".

5) Now dynamically create a continous form with each seat as a field.
6) Dynamically set your conditional formatting. Red for reserved, green for available, grey for DNE.
7) Now you could double click on a "cell" and return easily which row and seat it is. Click to reserve the seat and set it as "Temp Reserved".
8) When all done commit the change and write to the "tblSeatingGrid" and your actual reservation table.

If this idea works then consider doing it with a flexgrid control which would allow for icons in the cell. I would have to play with that idea to see if it is doable in a flexgrid.

 

[MajP]

Played with this idea a little. Making a grid of every row and seat is very easy using a xtab.
1) build the query.

[code]
TRANSFORM First(QRY_OverallSeats.SeatId) AS FirstOfSeatId
SELECT QRY_OverallSeats.Row
FROM QRY_OverallSeats
GROUP BY QRY_OverallSeats.Row
PIVOT QRY_OverallSeats.Seat;

the output looks like

Row	1	2	3	4	5
1	1A01	1A02	1A03	1A04	1A05
2	1B01	1B02	1B03	1B04	1B05
3	1C01	1C02	1C03

I added a 3rd row for demo and only showed the first 5 seats.

Now make a table from this grid

SELECT qryXtabGrid.* INTO tblGrid
FROM qryXtabGrid;

3)Now read through the reservations using recordsets and put an R in the reserved seats.
Now your output looks like this

Row	1	2	3	4	5
1	1A01	R	R	1A04	1A05
2	1B01	1B02	1B03	1B04	1B05
3	1C01	1C02	1C03

4)Lets say the max seat number for any room is 25. You could manually build a form with 26 text boxes. One for each possible seat and one for the row. Personally I would do it in code to save time. That way if I mess anything up I run the code to rebuild the form and save a lot of repetitive tasks.
5)Set the forms recordsource to "tblGrid" and make all textboxes for the seats invisible. Also do not bind the textboxes.
6) As the form loads set the control source of the textboxes that have a corresponding field in the grid. You will use recordsets again to read each field in the tblGrid. Make the appropriate textboxes visible.
7) Use conditional formatting on each field R "red", "T", yellow, not null "green"
8)I have a FAQ in the access forum on how to simulate a control array so that you only have to write one event procedure that will work if any textbox is clicked
9)Click on a cell and run an update query in the reservation table to make a "T" (temp reservation). Click "T" again to reserve is using an update query. Click "R" to unreserve it. Each time requery the form
10) Put a button on the form to clear temp reservations.

Put all the above in a subform, and have another subform on the bottom to search for sequences.

This would be pretty easy. I think a flexgrid would allow you to do something similar, but you could add some bells and whistles.

If interested I have most of this code done.

 

[bartus991]

Your idea sounds great. below (and attached) a new sample of the seatplan I have in mind.

The picture is based on a real seat booking system and works as follows. If I want to reserve two seats, I simply click on one of the available seats. This seat and the next one (as selected 2 seats) will be booked.

The idea I had about the switrchboard thing was following:
I create a Picture for every seat. Every picture has the same name as the seat ID. For example:

Private Sub FillOptions()

Const conNumButtons = 600

Dim con As Object
Dim rs As Object
Dim stSql As String
Dim intOption As Integer

Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Zaal 1]"
stSql = stSql & " ORDER BY [SeatId];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

If (rs.EOF) Then
End
Else
While (Not (rs.EOF))
Me(rs![seatID]).Visible = True
If rs![Status] = "R" Then
Me(rs![seatID]).Picture = "C:\SteatBlocked.bmp"
Else: Me(rs![seatID]).Picture = "C:\SeatEmpty.bmp"
End If
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing
Set con = Nothing

End Sub

This is just a start but in total there are about 10 different seattypes with each there own image.
That's why I didn't make a grid, I want to create an image of the room, the place where each seat is located and for example for some rooms a aisle between the seats.

Hope to have give you an idea what I would like to create. If needed I will create a sperate form for each room, but It would be nice if there is a option to use a standard form that will automatically alter it selfs to the configuration of the room.

This seatmap will be located on a form and will be seen after selecting an the event, date and time and after selecting the quantity of seats that will be needed (diverted in pricing groups) but for the seat map it's about the total of seats needed.

Please keep the code in mind as I see the idea of the above grid (in code) as an alternative.

 

[MajP]

What you are suggesting may actually be a lot easier. Since this discussion started off as Access and you mentioned an Access Switchboard, I was assuming an Access form. But if it is a VB form with an Access backend I would think it is easier because you can create control arrays.
Is this a VB form or Access form?
If this is an Access form I am just not sure about performance. I have never tried to put 500 image controls on an Access form and give each it owns image. May work fine. Now remember Access has a 750 control limit in the life time of the form. So that includes the ones you delete. So this is how I would do it.
1) I would write code to build a form for each room. You run the code and you should be basically be able to build something like you show with the proper amount of rows and columns and default images.
2) Once the form is built you can tweak the form to get it the way you want. If you mess up or the form gets corrupted run the code again to get your starting template.
3) if this is Access form you need to look at my FAQ on simulating a control array, because if not you would have to write 500 event procedures for your big room

Everything else is pretty easy after the form is built.