SQL query problem

[toastyman]

Hey to all!

I've got a bit of a problem with some SQL.. just wondering if I could get some pointers. Basically, I'm writing a query in Microsoft Access to take the number of seats on a plane from one table, and create all those seats in another table. The problem i've got is the second part... from what I can work out it needs to be some kind of for/while loop. Just wondering if anyone can make the following SQL work or give me some pointers:

Psuedo code:

WHILE (Select latest seat from seats table where flight id==1) < (Select number of seats from the aircraft table where aircraft id==1)
INSERT each seat into the seats table.. e.g.

Seat ID | Flight ID
   1          1
   2          1
   3          1
   4          1

Actual code so far:

WHILE (SELECT TOP 1 Seats.[Seat No], Seats.[Flight ID] FROM Seats WHERE (((Seats.[Flight ID])=1)) ORDER BY Seats.[Seat No] DESC) < (SELECT Aircraft.[No of Seats], Flight.[Flight ID] FROM Aircraft INNER JOIN Flight ON Aircraft.[Aircraft ID] = Flight.[Aircraft ID] WHERE ((Flight.[Flight ID])=1))

Cheers,
Rich

 

[toastyman]

Bump

No-one got any ideas?

 

[toastyman]

Sorry about that, i'll try and explain it better...

I've got a flights table, an aircraft table, a seats table and a passenger table. The flights table stores details about a flight, and the aircraft table stores details about the aircraft, including the number of seats. The seats table is used to store details about where passengers are sitting on the flight. The passenger table is used to store details about each passenger.

I need to write a query which inserts all the currently unoccupied seats on a flight into the seats table. `Aircraft`.`No Of Seats` stores the total number of seats on an aircraft.

When the query runs, it needs the user to specify the Flight ID. When they've done that, it needs to work out which seats haven't been allocated on a flight, and insert each seat into the Seats table, leaving the `Passenger ID` as blank.

Does that make any better sense?