Flight Card Database Design/Revision Suggestions

[LarryE]

@J.Burt
If you are still interested, I can do my best to convert your old table and field data to the new ACCESS tables.
Take a look at the file attached and you can see how the forms will work. This file also gives you the ability to open two reports that I created using a Reports Form.

Just open a new database and import your old tables. Then attach the file to a new post and I can see what can be done to ease the conversion for you.

 

[J.Burt]

@J.Burt
If you are still interested, I can do my best to convert your old table and field data to the new ACCESS tables.
Take a look at the file attached and you can see how the forms will work. This file also gives you the ability to open two reports that I created using a Reports Form.

Just open a new database and import your old tables. Then attach the file to a new post and I can see what can be done to ease the conversion for you.

Thank you for the offer. This is still a project I am slowly working on. While I would like for it to be done and not have to think about it anymore, I do need to understand and figure out some of the issues for myself, so I can speak intelligently about the database when I pass it on at some point.
With the assistance of the members here, I have an idea of the relationship structure needed to appropriately capture the data; however, I am having some difficulties making changes. For instance:

In the current database, one of the fields is labeled Mission, that is a combo box value list with seven criteria: Administrative, Fire, Law, Maintenance, Medivac, Rescue, and Training. For practice, I built a query to pull, each value out, and then build a table with MissionT containing MissionID(auto number primary key), MissionName, MissionDesc. In the Flight Card table, I then changed the Mission field to a Combo Box Table/Query with

SELECT [MissionT].MissionID, [MissionT].[MissionName] FROM MissionT;

Column Count 2
Column Widths 0";1"

It seems to work fine, but I can't apply any sort of relationship with Referential Integrity because the relationship must be on the same number of fields with the same data types. Not sure how to fix this. I know this will be a common problem as I make changes to the database.

Thank you again to everyone that has helped me so far.

 

[GaP42]

@J.Burt : This database project must be based on your knowledge of the business. If you accept the database structures as presented by the contributors here as addressing the shortcomings identified in the original, then unless you adopt those then you will likely be stuck in the past with a problematic db structure. That said, if adopted you will need to migrate existing data to provide continuity. The offer by @LarryE should be weighed up carefully - it can take a considerable amount of skill and expertise to massage the data from one database construct to another (if you are working with a live database then you need to develop and test the migration routines on a copy and when satisfied, execute on a changeover, and take account of issues that may arise. What you then need to comprehend and be able to communicate is the basis of the new structure and the forms/queries and reports built upon it (and be able to say what were the problems with the old that this addresses.)

Re one of your difficulties:

In the current database, one of the fields is labeled Mission, that is a combo box value list with seven criteria: Administrative, Fire, Law, Maintenance, Medivac, Rescue, and Training. For practice, I built a query to pull, each value out, and then build a table with MissionT containing MissionID(auto number primary key), MissionName, MissionDesc.

That is confusing: I presume you should have a MissionID attribute in your FlightT table (I have not checked your db) and you now wish to create a MissionT table to help manage the types of Missions (so maybe a name like MissionTypeT would be more aligned to what the table is for).
(You may not have the attribute MisionID in FlightT - you may be using the MissionTypeName).

Your combo as you say seems to work fine on the form to select a mission type for a flight. The referential integrity you require needs to be specified in the Relationships window. As said, if you have a MissionID in the FlightT table and a MissionID in MisionTypeT table then it should be straightforward to drag the MissionID from FlightT to MissionType and define the relationship 1: many. A Flight has one (principal) missiontype and a missiontype may be used to describe the purpose of many flights. All Flights must have a missiontype. So select the option that enforces a value in FlightT: only include rows where the joined fields are equal, and enforce referential integrity.

 

[J.Burt]

@GaP42

Thank you for your input.

The offer by @LarryE should be weighed up carefully - it can take a considerable amount of skill and expertise to massage the data from one database construct to another (if you are working with a live database then you need to develop and test the migration routines on a copy and when satisfied, execute on a changeover, and take account of issues that may arise.

I understand to the degree that I am able. The database is live, I do have a copy of the database, test on the copy, identify and fix issues, and then migrate/make changes to the live database. I realize the example given is very simple and I am using these small changes as a learning platform for other issues, such as whether I should even attempt to fix multiple multi-value lists that should be subforms with a many:many relationships.

You are right about the missing MissionID in the FlightT. I guess the question is, how and/or should I convert the MissionTypeName field to the MissionTypeID? or should I add the MissonTypeID field to the FlightT and then how would I relate or associate that with the MissionTypeName displayed so I don't manually have to change thousands of records?

I took over this database because administration asked for some statistics. I realized that those before me were just sorting the table and manually calculating the data or exporting to excel for a stats sheet. They were wasting a lot of time in manual calculations and making errors in the process. Why not let the software make the calculations for me, when I know the same thing has to be done every month? While doing this, I realized there were limitations due to the database architecture. Maybe too big of a task for me, but I would like to leave it better than I found it, when I end up leaving this assignment. I also run the social media and organize all of the training for the aviation unit. If interested, here is a link to our social media accounts, which show my primary responsibilities. Thank you again.

VC AirUnit Instagram/Twitter/Facebook

 

[LarryE]

J.Burt:

I remember this project and designed what I considered a pretty good application given the information we had. I don't remember if you actually looked at it or not but if you are interested here is the file with very limited test data. Maybe you can just play with it and learn how it works. The concept is pretty simple:

You have:

1. Multiple aircraft
2. Each with an hourly rate
3. Each rate has multiple flight operations
4. Each flight operation has multiple employees on board providing multiple services and could have one or more other passengers

 

[mike60smart]

@GaP42

Thank you for your input.

I understand to the degree that I am able. The database is live, I do have a copy of the database, test on the copy, identify and fix issues, and then migrate/make changes to the live database. I realize the example given is very simple and I am using these small changes as a learning platform for other issues, such as whether I should even attempt to fix multiple multi-value lists that should be subforms with a many:many relationships.

You are right about the missing MissionID in the FlightT. I guess the question is, how and/or should I convert the MissionTypeName field to the MissionTypeID? or should I add the MissonTypeID field to the FlightT and then how would I relate or associate that with the MissionTypeName displayed so I don't manually have to change thousands of records?

I took over this database because administration asked for some statistics. I realized that those before me were just sorting the table and manually calculating the data or exporting to excel for a stats sheet. They were wasting a lot of time in manual calculations and making errors in the process. Why not let the software make the calculations for me, when I know the same thing has to be done every month? While doing this, I realized there were limitations due to the database architecture. Maybe too big of a task for me, but I would like to leave it better than I found it, when I end up leaving this assignment. I also run the social media and organize all of the training for the aviation unit. If interested, here is a link to our social media accounts, which show my primary responsibilities. Thank you again.

VC AirUnit Instagram/Twitter/Facebook

Hi John
Did you ever look at the version that I uploaded?
I had added a Combobox to deal with Mission Types.