Access Database 3NF LinkTable Question

[tasmontique]

I am new to database design. I am creating an airline flight scheduling database in Microsoft Access 2002.

I am having trouble uniquely identifying an airline name, flight number in out, type of aircraft, arrival and departure times,routefrom ,routeto. All of this information is subject to change based on various factors.

I have attempted to bring the database to 3NF. I have tables for the following:

tblacCode
tblacFlightNumb erIN
tblacFlightNumb erOUT
tblacTypeOfAirc raft
tblacArrival
tblacDeparture
tblacRouteFrom
tblRouteTo.

I have a 1 to many relationship for the following
tblacCode 1: N LinktableFlight NumberINOUT

First---I would like to know if it is possible to use a linktable called treltblUniqueId entifier with a combination of all of the tables listed above primary keys to create a one to many relationship for tblacarrival, tblacdeparture ,tblacrouteFrom ,tblacrouteTo and tblDaysOFOperat ion?

Second--I set all the primary keys to autonumber but I am having trouble with the foreign keys autoupdating when I enter test data directly into access tables.
Should I have use text codes instead. These codes have a chance of repeating based on exceptions but rarely do and they would be more meaningful in my linktableUnique Identifier.



Please help.

[MMcCarthy]

I am new to database design. I am creating an airline flight scheduling database in Microsoft Access 2002.

I am having trouble uniquely identifying an airline name, flight number in out, type of aircraft, arrival and departure times,routefrom ,routeto. All of this information is subject to change based on various factors.

I have attempted to bring the database to 3NF. I have tables for the following:

tblacCode
tblacFlightNumb erIN
tblacFlightNumb erOUT
tblacTypeOfAirc raft
tblacArrival
tblacDeparture
tblacRouteFrom
tblRouteTo.

I have a 1 to many relationship for the following
tblacCode 1: N LinktableFlight NumberINOUT

First---I would like to know if it is possible to use a linktable called treltblUniqueId entifier with a combination of all of the tables listed above primary keys to create a one to many relationship for tblacarrival, tblacdeparture ,tblacrouteFrom ,tblacrouteTo and tblDaysOFOperat ion?

Second--I set all the primary keys to autonumber but I am having trouble with the foreign keys autoupdating when I enter test data directly into access tables.
Should I have use text codes instead. These codes have a chance of repeating based on exceptions but rarely do and they would be more meaningful in my linktableUnique Identifier.



Please help.

OK I think you have set up the structure wrong. You have broken it out too much.

tblFlight (Sample data in quotaton marks)
FlightID (Primary Key - Autonumber)
FlightNo "BA125"
IN/OUT "IN"
AircraftTypeID (Foreign key referencing the tblAircraftType table)
ArrivalDate "12/07/2007"
ArrivalTime "08:30"
DepartureDate "Null"
DepartureTime "Null"
RouteFrom "New York"
RouteTo "Null"

tblAircraftType
AircraftTypeID
Type "Boeing 747"

The FlightID is just the databases way to uniquely identify each record. Days of operation is a calculated value and shouldn't be stored but calcuated dynamically in a query. If the above structure doesn't fill your needs let me know what is missing.

Mary

[tasmontique]

Thank You so much. I simplified the tables and it works!!!

[MMcCarthy]

Thank You so much. I simplified the tables and it works!!!

Glad you got it working.

Mary