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.

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.

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

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.

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. ;)

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.

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

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

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

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.

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

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.

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.

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.

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??

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.

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

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

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.

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.

I tried it that way because I couldnt get the seat to be available for other flights once it had been booked for one.
So like if I booked seat Id 6 for flight 1, seat ID 6 was not available for flight 2.
Is there any way around this, ignore the database I just posted.
I am refereing to the one you posted before.
Andi.

hey ruralguy,
CAn you look at that problem, because it means that the whole idea doesnt work.
The database you queried, it displays all the seats yeah, but once a seat is booked for one flight, it is not availble for any others.
I dont understand why, as when a boking is made, a flight ID is taken, along with a seat id, so therefore the seat ID is booked for that flight, but why is it then not available on other flights.
Andi.

I'm looking into it.

I know why it yields the results but I don't know how to fix it without using a temporary table yet.

Thankyou.
THere is no way it should be this hard to do it, I was up untill 2.30am yesterday, and still wasnt getting anywhere.
Been on it all morning now aswell.
It's a case of, once the seat ID has been booked in for a flight, it's like the flight has stolen the seat ID and keeps it.
I cant figure out how to make the seat ID available to other flights.
I think it is something to do with my relationships again, or having a FK in the wrong table, something must be wrong like that.
I have actually tried almost every way though, really not sure where to go next, Im just going around in circles.
Considering building it from the ground up again, and maybe figure it out that way.
Really not sure what to do.
But I hope you understand the problem I am having.
I think that maybe another table, linking them, maybe, oh I really dont know.
But it should be possible to book the seat ID twice, surely.
Sorry for the rant, I just have had little sleep and really cant get my head around it.
Andi.

Sometimes Andi, I just walk away from the problem and the solution comes to me. I've been known to solve problem in the shower, or even in my sleep. I wish I were better at queries.

lol I wish I hadnt got myself into this mess.
Is there any recommendations on building it differently if I go for it a second time around.
So that seats can be booked for multiple flights.
Andi.

Make a link table... something like SeatFlight where u have the seat # and flight # keys

I tried that last night and it didnt work, becuase you cannot then display te details of all the available seats
:(

Okay, so that is good because it lets me book a seat more than once, which is exactly what I needed to do.
But now, what parameters do I need in a query to show not only the seats on a flight that have a booking, but also the ones that dont....?

Rural, here is the new db with the link included.
It means that seats can be booked for multiple flights.
Usefull indeed.
But I am not sure, on how to create the query so that it can show taken and not taken seats for a flight, same problem as the beggining.
I think once I can display that, I have solved the problem complertely.
I cannot forsee any other problems.
I have attached the database to see if you could come up with anything that could display the details I need.
Hopefully it will be finished this time!!
Andi.

Andi,
Does it need to be updateable, and if so, why?

It needs to be able to display all the seats for a given flight, with ones that havent been taken and ones that have.
And then I can use it as a subform for the booking form, and add in a booking into the slot that is available.
Then the next time I run the display it, shows that the new booking has been added in.
Andi.

Does the RecordSource for this "SubForm" need to be updateable? Are you planning to make changes right on the SubForm or on your MainForm and then just redisplay the subform? I'm playing with getting the records you need with a Union query but they are not updateable.

I have also been playin around.
I ran an append query on the list of seats query, and appended them to the seat flight table.
Then I could run that from a button each time a flight is added.
And I dont need the records updatebale right away I dont think no.
ANdi.

hey rural, hows the union query going?

I have attached the new database.
I have been working on a few things, like assigning staff to a crew number, so that a crew can be applied to flight.
I think I have done it correctly, but was just wondering if you thougt so too.
And also, I have the same problem with BOOKED query.
IT only shows the seat that has been booked, not all the seats available in seatflight.
This is starting to drive me crazy.
Andi.

Hi Andi,
Not having much luck in that approach either. Will work on the Temp Table approach next. Sorry. I'm convinced this can be done.

If you look at the new db I have a llink table between flight and seat that links to the booking.
Check it out.
that table shows every seat for every flight, i make it using the append query and entering the flight number.
Check it out, if you can get that table to display the data it has in it already, along with also which one has been booked.
Kerrchinggggg.
Andi.
Andi.

Ive Done It!

Those are nice words.