Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Philonaccess is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    8

    create a coach seating plan


    I am making a back office/booking system for a coach tour operator. I am having trouble getting my head around seat plans. For each tour I need to record which passenger is in which seat on the coach. Should I create a 'seats' table for each tour or a table containing every seat in the database with the tour ID to identify the tour. Whichever way I go, the coach seat plan for each tour is the same until I add passengers so I don't want to have to key in every seat on every tour each time I create a tour. Make Table query or append query??? Once I have decided how to store the data and create the table I will have to get on to how to select the seat during the booking process and display the seat plan graphically on screen.

    Anyone done anything like this can help. I am really not good with access but very keen!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You'd have a perm. TSeats table. If this is tied to a vehicle then the tSeats table would also have VEhID field from the tVehicles table.

    tVehicles table
    -------
    VehID, VehName,VIN, VehType,etc...
    22, Bus6, 123355, Bus,...

    tSeats table
    ------------
    VehID, Seat
    22, 1
    22, 2
    22, 3
    etc...

    The seat table is only the lookup table to book the trip. You need a tBookseats table to assign the seats for THAT trip.
    tBookings table
    BookID, ClientID,BookDate,Duration, VehID
    123, 99, 6\1\17, 3, 22

    then assign the seats to the tBookSeats table ,by appending the seat #s from tSeats table...
    BookID,VehID,Seat, ClientID
    123, 22, 1, 765
    123,22, 2, 334

    Then assign client to each seat.
    i used a form,pasted a graphic of the floor plan. Then manually assigned the seat textbox over top the graphic.
    Last edited by ranman256; 05-17-2017 at 01:06 PM.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might look at these data models at http://databaseanswers.org/data_models/index.htm

    http://databaseanswers.org/data_mode...ions/index.htm
    http://databaseanswers.org/data_models/railway_reservations/railway_reservations_physical.htm
    http://databaseanswers.org/data_mode...ions/index.htm
    http://databaseanswers.org/data_mode...ions/index.htm

    The railway reservations model seems close to the coach reservations. Might add a table for bus configurations - number of seats available - something like airplane seating. Planes have seating in 3-3, 2-5-2, 2-1 configurations plus the number of seats available.


    Good luck with your project and welcome to the forum.

  4. #4
    Philonaccess is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    8
    Thanks for helping!

    Seats will be allocated prior to a vehicle being allocated so I don't need to link a vehicle to a seat plan at present.

    If I am reading the solution right I would need a query on the lookup table. If the seat is occupied don't offer the seat. (to avoid overbooking a seat!).

    What I am still confused about, and I know I am being stupid, is how to create a full set of 48, empty seats for each tour without having to key them all in manually. So - when I create a new tour I add a set of empty but named/numbered seats to the seats table, each seat linked to the Tour by the tour ID.

    Sorry this is just messing with my head.

    Phil

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Few questions that might help others:
    1. Can their be multiple tours per day, per hour, can they be at the same time(different vehicles)?
    2. Is there only 1 vehicle or multiple?
    3. If multiple vehicles, do they each have same number of seats?
    4. Do you have to know a person is in Seat 3 and Seat 5 and the other ones are free? Or do you know you have 10 seats and you just need to start assigning seats until the 10 are filled?

  6. #6
    Philonaccess is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    8
    Hi Bulzie

    1. There are about 60-100 tours a year (they are holidays). various duration various dates, various destinations. Currently tours are all in a tours table.

    2. The vehicles are allocated at the last minute but currently they all comply with one seat plan. The numbering scheme is like an airline so 1A is row one nearside window then 1B, 1C and 1D is hext to the offside window. 13 rows (there is a central WC so 48 seats.

    3. Answer above

    4. Yes I need to ensure that the booking agent is not offered an occupied seat to prevent overbooking a seat.

  7. #7
    Philonaccess is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    8
    I have got as far as creating tVeh table which in my case is tcoachseatplans table.

    I have created a set of coach seat plans in a table.

    I have a seats table with a set of seats for each seat plan.

    From this I need to create a table containing every seat i have to allocate a client to. A set of seats from the seatplans for each tour. Basically each tour needs 48 records, one for each seat.

    That is where I am stuck.

    Sorry to be so dim but I have a real block on this.

    Phil

  8. #8
    Philonaccess is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    8
    Sorry I had to put this on the back burner for a while!

    I now have a query which has a seat plan for each tour. This is based on a seat plan table which contains all th seats available on all the tours. At the time of creation (using an append query) all the seats are empty. There is a field to enter the client ID (list box) and once entered the clients name is queried. So when I run the query for a given tour I see all the seats with the names of the clients. So far so good.

    I can't see how I can arrange these seats on a form in a grid so it is a simple graphic of the actial coach layout. Seats are named with a letter and number to depict the row and column. So the first row of 4 seats is 1A, 1B, 1C and 1D, the second row is 2A, 2B, 2C, 2D right up to row 13. (there is a gap of 2 rows - 4 seats - on one side for the side door). So a total of 48 seats.What I want to to see these seats on a form in the same positions as they are on the coach.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  10. #10
    Philonaccess is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    8
    Thanks but I don't think I need anything so clever - at least not yet! I can see that drag and drop is probably the answer for the future though. If I think I can do it now though I will.

    What I need, just to get it working, is a way of seeing the 'seats' laid out as they are on a coach in a sort of grid, with the occupier's name shown in occupied seats.

    I can arrange them on a continuous form with seat number (name) and the name next it but just as a vertical list.. and not in the right order.

    P

  11. #11
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    2. The vehicles are allocated at the last minute but currently they all comply with one seat plan. The numbering scheme is like an airline so 1A is row one nearside window then 1B, 1C and 1D is hext to the offside window. 13 rows (there is a central WC so 48 seats.

    13x4 = 52 but there are 48. What is the WC?

    Edit: is that wheelchair so no seats on that row?

  12. #12
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    My suggestion:

    tblTour: TourID(autonumber), TourDate, TourTitle, TourDescription
    tblVehicle: VehicleID(autonumber), vehicleLicense, VehicleDescription
    tblCustomer: CustID(autonumber), CustFName, CustLName, CustAddress, CustEmail, CustPhone
    tblSeat: SeatID(autonumber), TourID, VehicleID, s1A, s1B, s1C, s1D, s2A, s2B.... s13D

    Forms:
    frmTour(tblTour) - put a search lookup by Tour combo box in header section to allow you to search for a specific tour. Add subform frmSeat in detail section.
    frmSeat(tblSeat) - this subform will link to frmTour by TourID. Add all the seat fields(make these combo boxes that list the customers from tblCustomer - use/store CustID) and arrange like layout of coach. In the linkMaster and LinkChild property fields add TourID. Also add combo box list field for Vehicle(store VehicleID) to select which vehicle is used for that specific tour.
    frmCustomer - you can add the customer information to this form so they can contact the customers if needed or send emails, etc. This list also feeds the combo box to add them to frmSeat.
    frmVehicle - add vehicle data to this form and use it as combo box select list on frmSeat to select which vehicle is used for that specific tour.

    So idea is you select a tour and it will show all the seats and then you can select customers to go into those specific seats.

    Another variation depending on if your tours are very static, you could be to make tblTour specific (without dates) and Select the Tour from a combobox on the frmSeat form and then add the tour date to table tblSeat and also frmSeat. What I mean by static is say you are touring Boston, are there multiple Boston tours with different routes or stops or will there just be 1 tour of Boston.

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Phil,

    Please post a copy of your evolving database.
    Also, the first link I showed isn't drag and drop.

  14. #14
    Philonaccess is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    8
    No - sorry - Toilet and side door. so there is a break of 2 rows on one side, half way up the coach. One side has 13 rows = 26 seats the other side has 11 rows = 22 seats.

  15. #15
    Philonaccess is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2017
    Posts
    8
    I had another look at your replies and think the sample db could be useful. Except I don't understand the code very well but I will spend some time on it and try to work it out. It is certainly slicker and a lot less cumbersome than my efforts.

    It also approaches the problem from the opposite way. I have created all the empty seats for each tour in a table. Then I allocate a client to the unoccupied seat when the client books the tour. I guess I couldn't think of a way to prevent allocating a seat to more than one client otherwise.

    I will see how to sent my database.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-17-2017, 03:45 AM
  2. Floor Plan Report- Help
    By goocran7 in forum Reports
    Replies: 13
    Last Post: 01-26-2017, 09:22 AM
  3. Replies: 14
    Last Post: 12-28-2015, 07:51 AM
  4. MLM binary plan
    By DARTHSTRIDER in forum Programming
    Replies: 1
    Last Post: 02-14-2012, 09:41 PM
  5. Check boxes for seating plans
    By carlpots76 in forum Access
    Replies: 2
    Last Post: 01-16-2010, 07:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums