Flight Card Database Design/Revision Suggestions

[Pat Hartman]

I think I might have the schema go
Operation-->Employee--Activity rather than Operation-->Activity-->Employee

It makes more sense to me logically to have people assigned to the aircraft than activities. Especially since one person might perform more than one activity over the course of the mission.

 

[LarryE]

I think I might have the schema go
Operation-->Employee--Activity rather than Operation-->Activity-->Employee

It makes more sense to me logically to have people assigned to the aircraft than activities. Especially since one person might perform more than one activity over the course of the mission.

Pat:
I have a seperate employee table. The crew member performing any one or more activity during the flight operation is selected via combo box control based upon the employee table. Enter data only in one place. So we enter the employees first then they can selected on any flight operation for any activity. So I guess I have what you suggested. The employee table is linked to the activity table only.

Really, this is a pretty simple design as ACCESS goes. I think it will make much more sense when the input forms are presented. I will do that.

 

[Pat Hartman]

Larry, I was referring to the diagram you posted back in #14

 

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

 

[Pat Hartman]

Larry, I am NOT the OP. I was commenting on your posted suggestion for a schema in #15. You have the crew member connected to an activity and the activity connected to the operation. It should be activity connected to crew member and crew member connected to operation. i have no idea what you used when you built the form above but it looks like the crew and activity are independently connected to operation and that is probably not right.

I am trying to point out what is a logic error in the relationships. Having the crew member connected to the activity and having the activity connected to the operation isn't awful and will essentially work but I think that once the data is in, it will make more sense logically to swap the two tables in the hierarchy.

I am going away now unless you want to discuss that specific point.

 

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

 

[Pat Hartman]

Shortcuts were takes so I disagree with the schema. I suggest that the OP check very carefully that the schema works for reporting and analysis and won't be a problem later. There should be a table to define the types of flight activities. This should not be free form. That just allows typos and makes analysis a nightmare.

You should have four master tables that define entities -
tblHelicoptors
tblEmployees
tblFlightActivity (this could be a value list if it is short but value lists are problematic if they need updating so I stick to tables)
tblPassengers

One transaction table -
tblFlightOperations

And three junction tables.
tblFlightOperationsToPassengers (or current tblPassengers - see below)
tblFlightOperationsToEmployees
tblFlightOperationsToEmployeesToFlightActivity

If this is purely a rescue operation then hopefully, you won't have repeat passengers. If that is the case, then a stand alone Passengers table can be omitted and the junction table can include detail passenger data as in the current schema.

I would also change the name of tblHelicoptors to tblVehicles or something less specific to support other types of transport should they become relevant.

 

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

 

[Pat Hartman]

By shortcut, I meant combining tables that should probably be split and doing things like typing in the flight activity rather than picking from a table. I agree, the version posted by mike is better.

Althout things like InjuredTransports and Non-injuredTransports -- these should be counted from the passengers rather than entered separately. Also, "-" is one of those pesky nonAllowed characters in VBA. Names should contains ONLY letters, numbers, and the underscore. NO other characters. And, they should start with a letter.

 

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