Need help Visualizing the correct relationship or table break down. (newbie)

[jadehawk]

Hello everyone. I am trying to organize a complex(from my point of view) amount of data and have come to a standstill. This data will contain info from different stations (ex. Miami, Tampa, Orlando.....). I believe that my approach of how I created my tables is not the best proper layout. This DB will help me organize the data from a small aircraft repair company were I just started working and all the records are a mess in multiple exel sheets where you are force to open different excel documents to make sense of the report legend they where created a long time ago by an employee that no longer works there.

I have created 4 main tables:


tbl_EmployeeDetails (contains EmployeeID which is unique and The Base Station where he/she works)
tbl_FleetQualifications (contains Training on different fleets by hours (example: B747-400, B747-800)
tbl_AirlineQualifications (contains Training (yes/no) by different Airlines and fleet (example Airfrance_B747, Airfrance_A340)
tbl_OnCallAuthorizations (contains Authorizations to work on particular Airline and specific fleet)

Every thing was looking good until I was asked to add a way to record not just the fleet the employee was qualified for but also the combination of fleet and engine!. this is where all hit the fan for me as I can't decide if I should make a single table including every single fleet we work on and every single engine combo..
I started by making individual table for every fleet example:
tbl_A300PlusEngine
ID - AutoNumber
BaseStation - Text
EmployeeID - Number
EmployeeName - Text
PW-JT9D SERIES - Text
GE-CF6 SERIES - Text
This forced me to create a total of 27 Tables just for each fleet+engine I ran into a problem when linking the EmployeeID from the tbl_EmployeeDetails as I can not link more than 30 tables to an Index field (That is how I understood the error) I remove the primary key from EmployeeID and added a separate RecordID field. Now I can link the tables using EmployeeID without the 30 cap. but I'm not sure this is the best as EmployeeID is the unique field across all stations.

I am Still comfused on the proper use of Relationship.
Does anyone that understands what I am trying to accomplish here have a better Idea on how I should group all this info? I am still at the early stage and will benefit from any input.

I do want to make it clear that I want to learn to do this myself and that all your help will be appreciated. I have included a copy of what I have so far (via dropbox and mediafire as db file is beyond site limits), plus a jpg of the relationship page if it helps to visualized my intentions.

Thank you all for your time.

Dropbox Folder

MediaFire

[ssanfu]

Here are some sites on normalization and database design:

http://www.utteraccess.com/forum/Tre...n-t197282.html
http://forums.aspfree.com/microsoft-...es-208217.html (PDF)
http://www.utteraccess.com/forum/Aut...t-t443604.html
http://www.accessmvp.com/Strive4Peace/
http://support.microsoft.com/kb/283878

Read these.... then read them again. Really. I have, many times.

And yes, current design is not correct. Instead of 27 tables, there should be one table for the fleet.
Read about normalization, then post back with the design and any questions. I'm sure you will have a few...

[jadehawk]

@ssanfu
Thank you for the links I gave them a read and a lot of excellent info for new access user like me. I tried to use the new info learned and have redesigned the tables as follow. I think I got the core idea of it but maybe I am still in the wrong what do you think?
Currently I am trying to create a query that will only display one single employee and only show the fleet&engine that he/she is trained on. but I can only get the info from the employee and all the training details including the fields(fleet&engine) that he/she is not trained for. can this be done? I tried using the criteria on the query design but can't seem to find a way to "hide" the fields where the data store is "No" and only display the fields that have data "Yes".
I even changed the data type from checkbox to simple text but even this does not help.

Thank you for any input..

P.S.
Thank you for the read once again this sure beats having 27 tables

[ssanfu]

Can you save it in A2007 format?



Your tables are still not right. Take t_AirlinesCustomers. What happens if you get another airline customer? You will need to modify all your queries, forms, reports and code.

The fields should be more like:

AirlineCustomerID (autonumber) PK
CustDesc (Text)
ICAO (Text)