?

Log in

No account? Create an account
Anyone have significant experience with MS Access? I'm trying to… - 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.
linkReply

Comments:
[User Picture]From: annicat
2008-06-02 08:02 pm (UTC)
I'd have to actually look at it. Hard to visualize. Also don't want to infect you with the crud that I have. Can you wait until next week?
(Reply) (Thread)
[User Picture]From: arcsine
2008-06-02 08:44 pm (UTC)
Yeah, if you've got some extra time next week I'd really appreciate if you could look at it with me.
(Reply) (Parent) (Thread)
[User Picture]From: annicat
2008-06-03 12:25 pm (UTC)
deal
(Reply) (Parent) (Thread)
[User Picture]From: randybamf
2008-06-02 08:08 pm (UTC)
What kind of data structure are you looking at? What you're describing sounds something like this:

Passenger
---------
PASSENGER_ID (number, PK, autonumber)
FIRST_NAME
LAST_NAME
PASSPORT
TSA_INFO
etc.

Flight
-------
FLIGHT_ID (number, PK, autonumber)
FLIGHT_NUMBER
AIRLINE
DATE
etc.

Flight_To_Passenger
-------------------
FLIGHT_ID
PASSENGER_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.
(Reply) (Thread)
[User Picture]From: arcsine
2008-06-02 08:44 pm (UTC)
So basically if the passengers and their specifics are pre-filled, and the records in the DB are per flight with plugged-in values from the passenger table, the final record concatenates the two?

Sorry, I'm database dumb, so this may make no sense. I'm used to doing things like this with Excel fill-downs.
(Reply) (Parent) (Thread)
[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:

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

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

Flight_to_Passenger
-------------------
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)