Seat Booking Problem, Help Needed.

[sideprojekt]

I have been trying to develop a system by which a air flight company can book seats on a plane for passengers.
I am having the problem with one of the requirments though, in that I cannot figure out how to display all the seats on a flight, as well as the ones that have already been booked.
This is required so that a form can be created for the user, so that they can give information back to customers on whether there is availble seats of their class type or not.
I have attached the database with this thread, if anyone could be of any assistance, it would be greatly appreciated.
Andi.

 

[RuralGuy]

Hi Andi,
You really need to give the problem the old college try or people will think you want them to do the work for you instead of just helping you over a tough spot. I can find no saved effort in your db to create what you want so we can offer suggestions on how to proceed.

 

[sideprojekt]

In the queries, I have ran a query which comes back with seats that have been booked for the flights in question.
I can oviously put in a parameter to enter flight ID, if I wanted a specific flight, but that will still only display the seats that have been boooked.
I can display all the seats, for a flight, by just using a query for seat ID and Flight ID, but it will not then tell me which have been booked or not.
I have tried this over a million times, and I really am left with nowhere to go.
I have tried ussing some link tables, but with no such luck.
I think I might need another table though which shows the flight ID, seat Id and whether it is booked or not, but raelly have no clue on how to set that up so it displays the data I need.
If I could run a query so it displayed the oposite of what the query I have saved does, it would show the seats that have NOT been booked, rather than have, but really dont know how to do that.
I am raelly only looking for a pointer, whether I need another table, or I need a nother field, or what critrea I need in a query.
I do want to do this myself, I just came here looking for some help, that is all.
Thankyou.
Andi

 

[RuralGuy]

Thanks for the update Andi. Give me a few minutes and I'll see if I can come up with a query you can use.

 

[RuralGuy]

Do both aircraft have identical seating or do we need to set up a relationship between seating and type of aircraft? BTW Boeing is the way they spell it.

 

[sideprojekt]

lmao well it seems my spelling is worse than my problem solving skills.
thankyou for pointing that one out anyhow, an embarrising mistake to leave in there... haha.
Well, it has taken me a while to get a seat not to be booked on two flights, I did initally want a different seating plan for differnt planes, just to show variety, but its not a major need.
The one that had me is the displayed all seats, booked or unbooked, for a particular flight.
I am actually laughing at my poor spelling, its made me chuckle.
Andi.

 

[RuralGuy]

Here's a start query with SeatBooking but not by flight number yet and I have to leave for a few hours. Sorry.

 

[RuralGuy]

OK Andi,
I believe I have the SeatBooking query working as you wanted.

 

[sideprojekt]

Thankyou so much, by the looks of things, that is exactly what I was looking for.
It's the IsNull one I didnt know how to do, even though I knew that was what I needed.
Your a star, ill just change the 1 in criteria now to [Enter Flight Number] and it'll be perfect, meaning the user can enter any flight number and return seats.
Your a star.
And ideas on how I can get the different number of seats, for different planes at all?
Pretty sure id need a relationship between plane and seat, but not sure really which one will hold the FK to allow it.
If not, no worries my man, youve been a huge help already.
Andi

 

[RuralGuy]

Glad I could help. I don't have an answer for you on the seats/plane problem but I'm pretty sure the seattbl table will have the FK. Actually, I believe having separate records for the seating in 747's vs. 757's or whatever in the seattbl table would work.

 

[sideprojekt]

seating plan as PK, seat ID, seat row, class ID and plane ID??

 

[RuralGuy]

YEP! It looks like it would work to me. Just don't try to get a seat to do double duty, even if everything is identical in several planes. A separate row in the table for *every* seat in *every* plane.

 

[sideprojekt]

Not sure I know what you mean there.
I made the above, but it just exchanged the seat Id to Seating plan.
Not sure if that is the way to go about it, maybe I just set it up wrong.
maybe a link table with seatingplanID, plane ID?
Not too sure, little lost again..
could create an identical table as the seating one, and then just extend the rows and seat id's etc and then link it with a link table?
Whats your opinion mr genieus?
Andi.

 

[RuralGuy]

On second thought, I don't think you want a "seating plan" PK. Just leave the SeatID as the PK for that table and add the PlaneID as a FK to each row.

 

[sideprojekt]

so I would need say, row a-G on plane ID 1, with seats Id's to 59, then rows A-G on plane id 2, with seat ID 60 to 150 say??

 

[RuralGuy]

If one plane had 100 seats and another plane has 150 seats, your table will have 250 rows with 100 of them have a PlaneID of 1 and 150 rowns with PlaneID of 2. Then all you need to do is say for PlaneID 1.

 

[RuralGuy]

There will be as many records in the table as you have plane types times the seats in them.

 

[sideprojekt]

I have discovered a fundemental flaw in the system.
If you run your query again, and look at flight ID 1, there is a booking for seat ID 5.
If you then run the query for any other flight, the seat ID 5 has disappeared.
Any sugggestions why?
Andi

 

[sideprojekt]

got to this now.
Changed things around, but now am having the double booking problem rather than just one booking for each seat.
Also I have added in the plane ID too so tht differnt seating plans can be done.
The new query query1 will show you the problem.
Any ideas on where I am going wrong, and how to get around it.
Andi.

 

[RuralGuy]

How come you dropped the Row column? Why are the PassbookID and PassengerID fields in the seattbl at all? This table should *only* have seats and aircraft information. You should try and eliminate the embeded spaces in any names as well. They *will* give you unexpected grief.