Log in

No account? Create an account
The Veritable TechNinja [entries|archive|friends|userinfo]
The Veritable TechNinja

[ website | ~/public_html ]
[ userinfo | livejournal userinfo ]
[ archive | journal archive ]

[Jun. 2nd, 2008|03:41 pm]
The Veritable TechNinja
[status |confusedconfused]
[waveform |Bailey - Pushin' Beatz (on Pandora)]

Anyone have significant experience with MS Access? I'm trying to develop an airline passenger manifest database. Records should show all passengers of each flight, as well as a ton of stats both on the person and the flight. The issue is avoiding massive duplicate data entry.

What I'd like to do is make an entry to the database per passenger per flight, but have the stats of the flight populated automagically. Basically the form has two parts:

The flight info, which will be the same for all records entered per session (for each passenger).

And the passenger info, passport numbers, TSA clearances, &c.

Then the record contains all the data per passenger, so that reports can be generated saying what flights on what days what guy took without having to make the passenger names fields instead of whole records. Or should I just dump this idea and make a bunch of "passenger 1", "passenger 2" fields in the records? I would, but I imagine it'll make querying a bitch.

Anyone know how to do this? Once I have the input form that auto-populates the records with the data that's the same for all passengers of that flight, making a report will be as easy as picking a flight number, date range, passenger name, &c.

[User Picture]From: randybamf
2008-06-03 12:25 pm (UTC)
What you're describing is pretty much how it would work. The two tables would be joined (using the third table that just has IDs) and the final records would contain both the Flight and Passenger info. Here's an example:

1 John Doe etc, etc
2 Jane Smith etc, etc

1 USAir 1234 Texas 5/30/2008
2 NWA 5678 Idaho 6/1/2008

1 1
1 2
2 1

In this scenario, the first flight has both passengers (1 and 2) and the second flight only has passenger 1. Now, if you do a full select (would look something like this in Access' SQL query window: SELECT Flight.*, Passenger.* FROM Flight, Passenger, Flight_To_Passenger WHERE Flight.Flight_ID=Flight_to_Passenger.Flight_ID AND Passenger.Passneger_ID=Flight_to_Passenger.Passenger_ID

You'd get something like this:
Flight_ID Airline Fl_Num Dest Date Passenger_Id FirstName LastName
1 USAir 1234 Texas 5/30/2008 1 John Doe
1 USAir 1234 Texas 5/30/2008 2 Jane Smith
2 NWA 5678 Idaho 6/1/2008 1 John Doe

So each returned record has the flight information repeated for each passenger.

(Reply) (Parent) (Thread)
[User Picture]From: arcsine
2008-06-03 01:50 pm (UTC)
Yes, that's exactly what I need. You're a DB genius man!
(Reply) (Parent) (Thread)