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.

Flight_To_Passenger-------------------FLIGHT_IDPASSENGER_ID(dual PK made up of both fields)

The form would then act mainly on the Flight table, with a pick-list of passengers pulled from the Passenger table (and possibly a button to add a new passenger). Saving would then save everything to the Flight table except the passenger list which would go to Flight_To_Passenger.

Querying is ultra-simple in this case as you just join all three tables on FLIGHT_ID and PASSENGER_ID and poof, everything is available.

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:

Passenger---------1 John Doe etc, etc2 Jane Smith etc, etc

Flight------1 USAir 1234 Texas 5/30/20082 NWA 5678 Idaho 6/1/2008

Flight_to_Passenger-------------------1 11 22 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