If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
do i need six tables, or one?
First I wrote:
I am working on a database that seats passengers and pilots on hot air balloon flights. I have six tables for flights, one for flights with 1 passenger, 2 passengers, etc all the way to 6 passengers. One of the necessary functions of the database is to seat passengers that will approach a check in table on open flights. I have a system set up right now that scans the fields "Passenger1" through "Passenger6" in each flight record and determines whether ANY of those fields are empty. If any of the fields turn up empty, an update query turns the "Flight Full?" check box to "NO" for that flight. However, if all "Passenger1" - "Passenger6" fields are full, the update query changes to "Flight Full?" check box to "YES." This way when I check for Open flights, I do indeed get flights with any sort of opening, but I have no way to pinpoint exactly how many open seats i'm looking for. What I want to do from here is find a way to count how many of the "Passenger" fields in each record are indeed a null value. This would enable me to prompt the user for how many empty seats they need to find in a flight. This way, if they need three empty spots, this query would scan the tables and determine which flights have three openings. Thanks in advance for reading down this far, please offer me any possible ideas you might have. Thanks, Steve Then Jeff wrote: Steve Your data structure is fine ... for a spreadsheet! You don't need to (and don't want to) use repeating fields (Passenger1, Passenger2, ...) in a relational database. Instead, create a table that has Flight & Passenger as fields, then use a query to count how many Passengers you have for any given Flight. -- Good luck Jeff Boyce Access MVP To which I reply: Jeff, The problem is, some flights seat two passengers, so I need to have a flight table to accomodate two spots, and the same case for a six person flight, right? To know which passengers are seated in each "flight" record, wouldn't I have to have fields under each "flight" record to accomodate that data? I would really appreciate it it someone who bothers to post would spend more time helping more descriptively and less time scoffing at my very limited database talents. I understand that I don't know too much about "data theory," but I am having trouble understanding how "creating a table with 'passenger' and 'flight' as fields" will allow me to count how many spots are open on records for flights, each of which accomodate different numbers of passengers...? I tried having a simple database where it was as easy as "flight" and "passengers," but since some flights hold 4, some 3, some 6, etc, I was told by some other access mvp that I had to have a different table for each seating capacity. For instance "Flights4" is my table for four person flights. "Flights6" is my table for six person flights. I was to scan these tables to find out EXACTLY how many "null"s appear throughout "passenger1" - "passenger6" (for six person flights, "pass1" - "pass5" for five, etc.) Let's say we have a six person flight with four spots blank. How could you even accomodate that without having a "flight" record with the fields "pass1, pass2 - pass6" to hold those passengers names (which are looked up from the "passengers" table.) My sincerest thanks to those willing to read carefully and respond -Steve Anyone? :-) |
#2
|
|||
|
|||
do i need six tables, or one?
Steve,
I'm no expert by any means, but I would recommend the following: Flight table: primary key on flightnumber flightnumber numberofseats Passenger table: primary key on Flightnumber, PassengerName Flightnumber PassengerName Query:Include both tables, joined on Flightnumber. Include flightnumber, numberofseats, Passengername. Create a new field flightfull:IIF(CountofPassengername = numberofseats,"Full","Not Full") Change it to totals query, group by flightnumber, numberofseats, count Passengername, and set flightfull to an expression. Hope this helps. -----Original Message----- First I wrote: I am working on a database that seats passengers and pilots on hot air balloon flights. I have six tables for flights, one for flights with 1 passenger, 2 passengers, etc all the way to 6 passengers. One of the necessary functions of the database is to seat passengers that will approach a check in table on open flights. I have a system set up right now that scans the fields "Passenger1" through "Passenger6" in each flight record and determines whether ANY of those fields are empty. If any of the fields turn up empty, an update query turns the "Flight Full?" check box to "NO" for that flight. However, if all "Passenger1" - "Passenger6" fields are full, the update query changes to "Flight Full?" check box to "YES." This way when I check for Open flights, I do indeed get flights with any sort of opening, but I have no way to pinpoint exactly how many open seats i'm looking for. What I want to do from here is find a way to count how many of the "Passenger" fields in each record are indeed a null value. This would enable me to prompt the user for how many empty seats they need to find in a flight. This way, if they need three empty spots, this query would scan the tables and determine which flights have three openings. Thanks in advance for reading down this far, please offer me any possible ideas you might have. Thanks, Steve Then Jeff wrote: Steve Your data structure is fine ... for a spreadsheet! You don't need to (and don't want to) use repeating fields (Passenger1, Passenger2, ...) in a relational database. Instead, create a table that has Flight & Passenger as fields, then use a query to count how many Passengers you have for any given Flight. -- Good luck Jeff Boyce Access MVP To which I reply: Jeff, The problem is, some flights seat two passengers, so I need to have a flight table to accomodate two spots, and the same case for a six person flight, right? To know which passengers are seated in each "flight" record, wouldn't I have to have fields under each "flight" record to accomodate that data? I would really appreciate it it someone who bothers to post would spend more time helping more descriptively and less time scoffing at my very limited database talents. I understand that I don't know too much about "data theory," but I am having trouble understanding how "creating a table with 'passenger' and 'flight' as fields" will allow me to count how many spots are open on records for flights, each of which accomodate different numbers of passengers...? I tried having a simple database where it was as easy as "flight" and "passengers," but since some flights hold 4, some 3, some 6, etc, I was told by some other access mvp that I had to have a different table for each seating capacity. For instance "Flights4" is my table for four person flights. "Flights6" is my table for six person flights. I was to scan these tables to find out EXACTLY how many "null"s appear throughout "passenger1" - "passenger6" (for six person flights, "pass1" - "pass5" for five, etc.) Let's say we have a six person flight with four spots blank. How could you even accomodate that without having a "flight" record with the fields "pass1, pass2 - pass6" to hold those passengers names (which are looked up from the "passengers" table.) My sincerest thanks to those willing to read carefully and respond -Steve Anyone? :-) . |
#3
|
|||
|
|||
do i need six tables, or one?
Steve,
You could do this with six tables, but Jeff is correct about that not being the best way to handle it. It causes a whole lot more problems than it solves. What I would recommend would be a single table to hold all of the passengers. Basically it would need three fields: an AutoID, FlightID, and PassengerName (and perhaps contact info for the passenger). You would need another table to hold FlightID and a Capacity (and likely date and maybe other info about the flight). You could query the first table to count the number of passengers already booked to each flight. This result set could then be compared to the Capacity in the other table to allow you to select an appropriate flight to book an additional group of people. This has its own issues (perhaps the primary one being it would be possible to add more passengers than the flight could accomodate, so you might want to run another query that would check the data for cases where # booked exceeds the Capacity just as a double-check), but would be a lot easier to deal with down the road. FWIW... Rob -----Original Message----- First I wrote: I am working on a database that seats passengers and pilots on hot air balloon flights. I have six tables for flights, one for flights with 1 passenger, 2 passengers, etc all the way to 6 passengers. One of the necessary functions of the database is to seat passengers that will approach a check in table on open flights. I have a system set up right now that scans the fields "Passenger1" through "Passenger6" in each flight record and determines whether ANY of those fields are empty. If any of the fields turn up empty, an update query turns the "Flight Full?" check box to "NO" for that flight. However, if all "Passenger1" - "Passenger6" fields are full, the update query changes to "Flight Full?" check box to "YES." This way when I check for Open flights, I do indeed get flights with any sort of opening, but I have no way to pinpoint exactly how many open seats i'm looking for. What I want to do from here is find a way to count how many of the "Passenger" fields in each record are indeed a null value. This would enable me to prompt the user for how many empty seats they need to find in a flight. This way, if they need three empty spots, this query would scan the tables and determine which flights have three openings. Thanks in advance for reading down this far, please offer me any possible ideas you might have. Thanks, Steve Then Jeff wrote: Steve Your data structure is fine ... for a spreadsheet! You don't need to (and don't want to) use repeating fields (Passenger1, Passenger2, ...) in a relational database. Instead, create a table that has Flight & Passenger as fields, then use a query to count how many Passengers you have for any given Flight. -- Good luck Jeff Boyce Access MVP To which I reply: Jeff, The problem is, some flights seat two passengers, so I need to have a flight table to accomodate two spots, and the same case for a six person flight, right? To know which passengers are seated in each "flight" record, wouldn't I have to have fields under each "flight" record to accomodate that data? I would really appreciate it it someone who bothers to post would spend more time helping more descriptively and less time scoffing at my very limited database talents. I understand that I don't know too much about "data theory," but I am having trouble understanding how "creating a table with 'passenger' and 'flight' as fields" will allow me to count how many spots are open on records for flights, each of which accomodate different numbers of passengers...? I tried having a simple database where it was as easy as "flight" and "passengers," but since some flights hold 4, some 3, some 6, etc, I was told by some other access mvp that I had to have a different table for each seating capacity. For instance "Flights4" is my table for four person flights. "Flights6" is my table for six person flights. I was to scan these tables to find out EXACTLY how many "null"s appear throughout "passenger1" - "passenger6" (for six person flights, "pass1" - "pass5" for five, etc.) Let's say we have a six person flight with four spots blank. How could you even accomodate that without having a "flight" record with the fields "pass1, pass2 - pass6" to hold those passengers names (which are looked up from the "passengers" table.) My sincerest thanks to those willing to read carefully and respond -Steve Anyone? :-) . |
#4
|
|||
|
|||
do i need six tables, or one?
I would recommend a structure as follows:
tblFlights ---------- FlightID FlightDate FlightCapacity .... tblPassengers ------------- PassengerID PassengerName ..... tblPassengerList ---------------- FlightID PassengerID In this structure tblPassengerList is a junction table between tblFlights and tblPassengers, contain just the primary key fields from these tables. With this structure you can easily find the number of passengers on each flight with the following query: SELECT FlightID, Count(*) AS SeatsFilled FROM tblPassengerList GROUP BY FlightID; Save this query as qrySeatsFilled, then you can create the following query to find the Seats Available: SELECT tblFlights.FlightID, [FlightCapacity]-[SeatsFilled] AS SeatsAvailable FROM tblFlights INNER JOIN qrySeatsFilled ON tblFlights.FlightID = qrySeatsFilled.FlightID; hth Chris -----Original Message----- First I wrote: I am working on a database that seats passengers and pilots on hot air balloon flights. I have six tables for flights, one for flights with 1 passenger, 2 passengers, etc all the way to 6 passengers. One of the necessary functions of the database is to seat passengers that will approach a check in table on open flights. I have a system set up right now that scans the fields "Passenger1" through "Passenger6" in each flight record and determines whether ANY of those fields are empty. If any of the fields turn up empty, an update query turns the "Flight Full?" check box to "NO" for that flight. However, if all "Passenger1" - "Passenger6" fields are full, the update query changes to "Flight Full?" check box to "YES." This way when I check for Open flights, I do indeed get flights with any sort of opening, but I have no way to pinpoint exactly how many open seats i'm looking for. What I want to do from here is find a way to count how many of the "Passenger" fields in each record are indeed a null value. This would enable me to prompt the user for how many empty seats they need to find in a flight. This way, if they need three empty spots, this query would scan the tables and determine which flights have three openings. Thanks in advance for reading down this far, please offer me any possible ideas you might have. Thanks, Steve Then Jeff wrote: Steve Your data structure is fine ... for a spreadsheet! You don't need to (and don't want to) use repeating fields (Passenger1, Passenger2, ...) in a relational database. Instead, create a table that has Flight & Passenger as fields, then use a query to count how many Passengers you have for any given Flight. -- Good luck Jeff Boyce Access MVP To which I reply: Jeff, The problem is, some flights seat two passengers, so I need to have a flight table to accomodate two spots, and the same case for a six person flight, right? To know which passengers are seated in each "flight" record, wouldn't I have to have fields under each "flight" record to accomodate that data? I would really appreciate it it someone who bothers to post would spend more time helping more descriptively and less time scoffing at my very limited database talents. I understand that I don't know too much about "data theory," but I am having trouble understanding how "creating a table with 'passenger' and 'flight' as fields" will allow me to count how many spots are open on records for flights, each of which accomodate different numbers of passengers...? I tried having a simple database where it was as easy as "flight" and "passengers," but since some flights hold 4, some 3, some 6, etc, I was told by some other access mvp that I had to have a different table for each seating capacity. For instance "Flights4" is my table for four person flights. "Flights6" is my table for six person flights. I was to scan these tables to find out EXACTLY how many "null"s appear throughout "passenger1" - "passenger6" (for six person flights, "pass1" - "pass5" for five, etc.) Let's say we have a six person flight with four spots blank. How could you even accomodate that without having a "flight" record with the fields "pass1, pass2 - pass6" to hold those passengers names (which are looked up from the "passengers" table.) My sincerest thanks to those willing to read carefully and respond -Steve Anyone? :-) . |
Thread Tools | |
Display Modes | |
|
|