Flight Card Database Design/Revision Suggestions

[LarryE]

This example shows Wilma Flintstone rescued using the Bell 206. It took 23.5 hours over two days. John Smith was both Medic and Winch Operator. At $1,000.00 per hour for this aircraft, the reimbursable cost of the operation was $23,500.00. You can see in the Employee Crew Activity Log the drop-down selection combobox control that allows you to select any employee. You can then either input Flight Activity OR if it has already been used, you can select one using the drop-down. Each Activity Log is linked to each helicopter and each flight operation. If you select a different helicopter or different flight operation, these fields will change to reflect the data for only that helicopter and/or flight operation.

This is very preliminary. I don't know what every field means or if they are even needed. I just took what was indicated in the original post and used those fields. If this very preliminary design works for you, I can attach it and you can have it. I created no reports or other queries. But if you can present it on a form, then you can present it on a report also.

This form consists of a series of subforms which are linked to parent forms. Some are single forms and some are continious. I use continious forms if there is going to be many entries for any one parent record. In this case, each helicopter and each flight operation may have many crew activities, so continious forms are convienent.

 

[LarryE]

J.Burt:
Here is the file. It only has forms and tables.

 

[mike60smart]

Hi Larry
I would agree with Pat on this one as A Flight has specific Crew Members and each Member can carry out 1 or more activities.

 

[LarryE]

Hi Larry
I would agree with Pat on this one as A Flight has specific Crew Members and each Member can carry out 1 or more activities.

And that is exactly the way it works. Each helicopter has multiple flight missions. Each mission has multiple crew members and each crew member may perform multiple work activities. And each mission may have other passengers as well. Perfect.

 

[mike60smart]

Sorry but you have in your relationship diagram:-

Flight linked to Crew Activities

It should be Flight linked to FlightCrew

 

[LarryE]

The way I have this working is the CrewID in the TblCrewActivity is entered with a drop-down combobox using the CrewID from the TblEmployee. So any employee can be a crew member and perform any crew task on any flight on any helicopter. Then their individual activity on that flight is also a combobox selection that may be input OR selected fromthe list of previously defined tasks (activities). I suppose you could make a separate crew activity table as well, but I think it is unnecessary.

I do understand that you could have a separate table of flight crews. I didn't think that was necessary since you can pull flight crew members from the employee table (which I did think was necessary) and then make their assignment(s) on each flight. I just thought that may be the best way. I didn't realize I may have violated ACCESS rules. Sorry. My thinking was we are only tracking helicopters, operational missions, crew activities and other passengers. I included operation mission dates and times and calculate hours and costs based upon input per hour cost for each helicopter. That gives us both flight costs and hours worked for each crew member. I am worried that billing rates on each helicopter may change over time and we could lose historical cost data. I was going to make a separate helicopter cost table. It might still be necessary. I don't know.

Let's see what the OP says from here on out. LarryE clear.

 

[LarryE]

I had no clue this is to be considered a "shortcut" design. That most certainly was not the intention.

 

[mike60smart]

I Think the relationships in your revised db attached fits the requirement better.

 

[LarryE]

Totally agree about using special characters. I didn't change the names when I just copied the field names from the post. Yup 100% agree.
Mikes version restricts flight activity (he calls it CrewType) to only those activites defined in a list box. I give the user more flexibility by using a combo box that allows both data entry and selecting from the activities already defined. Using a list box, if a new flight activity needs to be defined, the user needs to open the form in design view and change the options in the list box. I don't like that, but OK. If you want to define flight activities in a table, that's OK too, but adds another layer.

Mikes TblFlightCrew does exactly the same things my TblCrewActivity does. I do have one extra field that is a description field (probably not necessary) from the original post, and Mike uses CrewType instead of FlightActivity. His CrewType is a listbox and my FlightActivity is a combo box. OK, stick a fork in me because I'm done.

 

[mike60smart]

Hi Larry

My Listbox is wrong and should be a Lookup to tblTypes.

We are only trying to get the relationships correct and believe me I have had many forks stuck in me.

 

[LarryE]

I understand completely. I don't see a TblTypes, but I guess you are going to create one?

 

[LarryE]

Pat and Mike:

1. Each piece of Equipment
2. Has a cost per hour rate
3. Each rate has flight operations
4. Each operation has flight activities and crew and passengers
5. Each crew member has one or more activity in TblCewActivity

It's getting complicated and don't know if the OP is following or not, I guess this design would work. Form input could get messy.

 

[J.Burt]

All of this has been really helpful. The discussion and being able to see how the tables are connected is greatly beneficial. I have yet to take a look at the files, but I will let you know.

 

[LarryE]

J.Burt:
Here is a file that should work and contains a report that lists some operations detail including hours and reimbursement amounts based upon hourly rates. You can filter by Equipment, by Operation, by Dates and by Requesting Agency.

 

[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.