Flight Card Database Design/Revision Suggestions (1 Viewer)

[J.Burt]

Good Morning, Afternoon, or Evening where ever this happens to find you.

I have inherited a database that was created with the intention for tracking flight and crew information for a helicopter operation. The database was designed by a member of our organization with minimal experience in database design. The database is very linear and includes some features that make it difficult to track the information we need. I attempted to strip down the database to attach but could not get it small enough to be practical and think a window snip of the primary form will suffice with some explanation. This database is used for billing and is also used to track the currency of personnel on board the helicopter. All of the data entered into this form goes to one table. See the form screen shot below: Please give me some grace, there are spaces, special characters and all sorts of other issues that I will need to change.

Table explanation:
ID (Auto number);
Flight date (date/time);
Aircraft # (value list);
Time Depart, Time Return, and Total Time (Date/Time);
After Hours (Boolean Yes/No);
HOBBS Start and HOBBS End (Number);
Total HOBBS (Calculated);
Pilot 1 and Pilot 2 (Value List);
Pilot 1 and PIlot 2 Activities (Table Multivalued Field);
Crew Chief 1 through 4; (Value List)
Crew Chief Activities; (Table Multivalued field)
Medic (Value List);
Medic Activities (Table Multivalued field)
Rescue Discipline (Table Multivalued field)
Equipment (Table Multivalued field)
Mission (Value List);
Activity (Value List);
Mutual Aid (Yes/No);
Requesting Agency (Value List);
Area (Value List);
RB#, VNC#, Incident Name (Short Text);
Hoist #, Hoist Cycles, Injured Transports, Non-injured Transports, JP-5 Gallons (Number);
Disposition (Value List);
OHV (Yes/No)
Remarks (Long Text)

I will give a couple of examples of the information I need to pull from this database. Every fiscal year we are reimbursed through the state (CA) for all OHV (off highway vehicle) related calls for service. We currently have 8 helicopters different hourly rates based on their make and model. I created a helicopter table with an auto number ID, helicopter number, make model and then related it to the Flight Card and to a created Rates table. Allowing me to pull all of the OHV calls per airframe, add the total times, and then calculate the total cost based on the defined rates. This took a little while for me to do, since I have no experience, and I have had to read and watch YouTube videos on Access, but all in all it was not too hard to complete. Here is a screen shot of the relationship window.

Second example includes information I have not had to deal with yet. For every flight we log information for the pilots, crew chiefs and medics and use it to track currency. Currently the only way set up to see the most recent information is to manually scroll through the database and find it. This is not ideal. For reference, if there is a rescue, the pilot and crew will be listed by last name and then under activities we will select multiple items such as NVG's, Hoist Operator, Hot Seat Rescue, ALS Assist, confined space landing, etc. Same process for each crew chief, pilot, and medic. The multivalue fields do not make this ideal; however, I know I can pull the most recent information with a Max function and then a second crosstab query to clean it up, but I would like to do it right or make it right if possible.

If you happened to make it all the way through this post, should I work on fixing the problems that exists, such as: converting value lists to tables and relating the relevant information? How do you think I should address the multivalue fields? I work off a copy/prototype of the database and cannot tell you how many times all off the information has disappeared or been deleted.

Should I continue letting the users use the current database and build a new one, obeying the Access Ten Commandments? I am open to any suggestions.

Thank you for your time and consideration. Unfortunately, hiring someone to do it for me is not an option. I can always try and provide more information upon request.

 

[Pat Hartman]

1. Get rid of the repeating group of Crew. You can leave Pilot (captain) as is since the crew can have only one caption. That also happens to be the minimum number of crew possible. Everybody else is "extra". Create a child table that can hold a row for each other crew member along with his official position. Leaving this as is will make it seriously difficult to track how a person worked during the year since I'm pretty sure that they don't always serve the same role on every flight.
2. Get rid of the multi-value fields. Create a separate table for each. Again. The picklist is cool but it is very hard to work with if you want to calculate statistics.
3. You can leave the combos for last since they are working now. I've included a sample database that may help you to simplify that.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

This app was created the first time in the 80's using COBOL and IMS (hierarchical database - precursor to RDBMS. Looks like an org chart. The relationships are fixed) and reincarnated several other times. Finally ending up in Access.
4. Life does not stop while you are developing. You will have to convert the existing data regardless so what difference does it make if you convert 500 records or 600?
5. Because of the conversion, I would probably not use staged releases. I would work until I am happy with it and so are the users. Then deliver.
6. However, since you will need to convert to get data to test with, I would start a database that will be used ONLY to handle the conversion process. You will add append queries and make table queries and possibly code. You need to be very careful because you will be constantly reworking this db as how it works could/probably will change over time. Just go back to the conversion table, modify it and do another conversion. If you don't build the conversion as you go, you will have to create test data and you STILL need to build a conversion that you can automate.
7. Fix the poor names as you work on each area.
8. If you come up with features that the current app doesn't support, collect the data on paper to be entered after the conversion is complete.

 

[J.Burt]

Thank you for the response Pat. I will have to decode some of this in my head and then ponder on it for a bit. Thank you for the sample database, I will definitely take a look at it.

 

[GaP42]

Just a couple of other observations - in addition to the wise comments from Pat.
Total Time is a calculated value and should not be a column in your table. Calculate on the fly in queries/forms/reports.
Your CWM rates table only supports a single rate for a helicopter - will the rates change from year to year? Perhaps an effective from date and effective to date fields are needed to support variation over time.
Perhaps it is just a simple rendering issue - but you have a relationship between Flight Card and Passenger based on ID in each table. These IDs are not the same. The Passenger table needs a FK to the Flight Card record ID. The IDs in each table should be named to represent what they are eg FlightID and PassengerID not simply ID.
Also not sure but the field Name aircraft # may cause problems -- as a genberal rule no spaces in field names. # is a special characteer thta may cause confusion to you and Access.

 

[MsAccessNL]

I see many problems. I think rebuilding the database from scratch is the best. If the layout of the forms suite you, then you could copy these to the new database. I am not working on a project currently, don’t mind helping you a bit (till new project hits my front door..). I will need a copy of your db with some sample data.

 

[J.Burt]

GaP42 thank you for your input and advice. Currently just trying to conceptualize what needs to be done and put a frame work on paper to work off of.
Ha ha!

I see many problems.

So do I, thank you for the offer to help.

 

[Cronk]

There are some redundant fields. Total Time can be derived. Also, I would guess, the fact of the flight being in Overtime would be determined by Flight Arrival/Departure times. I'd have all the crew/passenge in a separate table including the Captain

 

[LarryE]

Good Morning, Afternoon, or Evening where ever this happens to find you.

I have inherited a database that was created with the intention for tracking flight and crew information for a helicopter operation. The database was designed by a member of our organization with minimal experience in database design. The database is very linear and includes some features that make it difficult to track the information we need. I attempted to strip down the database to attach but could not get it small enough to be practical and think a window snip of the primary form will suffice with some explanation. This database is used for billing and is also used to track the currency of personnel on board the helicopter. All of the data entered into this form goes to one table. See the form screen shot below: Please give me some grace, there are spaces, special characters and all sorts of other issues that I will need to change.


View attachment 107502
Table explanation:
ID (Auto number);
Flight date (date/time);
Aircraft # (value list);
Time Depart, Time Return, and Total Time (Date/Time);
After Hours (Boolean Yes/No);
HOBBS Start and HOBBS End (Number);
Total HOBBS (Calculated);
Pilot 1 and Pilot 2 (Value List);
Pilot 1 and PIlot 2 Activities (Table Multivalued Field);
Crew Chief 1 through 4; (Value List)
Crew Chief Activities; (Table Multivalued field)
Medic (Value List);
Medic Activities (Table Multivalued field)
Rescue Discipline (Table Multivalued field)
Equipment (Table Multivalued field)
Mission (Value List);
Activity (Value List);
Mutual Aid (Yes/No);
Requesting Agency (Value List);
Area (Value List);
RB#, VNC#, Incident Name (Short Text);
Hoist #, Hoist Cycles, Injured Transports, Non-injured Transports, JP-5 Gallons (Number);
Disposition (Value List);
OHV (Yes/No)
Remarks (Long Text)

I will give a couple of examples of the information I need to pull from this database. Every fiscal year we are reimbursed through the state (CA) for all OHV (off highway vehicle) related calls for service. We currently have 8 helicopters different hourly rates based on their make and model. I created a helicopter table with an auto number ID, helicopter number, make model and then related it to the Flight Card and to a created Rates table. Allowing me to pull all of the OHV calls per airframe, add the total times, and then calculate the total cost based on the defined rates. This took a little while for me to do, since I have no experience, and I have had to read and watch YouTube videos on Access, but all in all it was not too hard to complete. Here is a screen shot of the relationship window.

View attachment 107504

Second example includes information I have not had to deal with yet. For every flight we log information for the pilots, crew chiefs and medics and use it to track currency. Currently the only way set up to see the most recent information is to manually scroll through the database and find it. This is not ideal. For reference, if there is a rescue, the pilot and crew will be listed by last name and then under activities we will select multiple items such as NVG's, Hoist Operator, Hot Seat Rescue, ALS Assist, confined space landing, etc. Same process for each crew chief, pilot, and medic. The multivalue fields do not make this ideal; however, I know I can pull the most recent information with a Max function and then a second crosstab query to clean it up, but I would like to do it right or make it right if possible.

If you happened to make it all the way through this post, should I work on fixing the problems that exists, such as: converting value lists to tables and relating the relevant information? How do you think I should address the multivalue fields? I work off a copy/prototype of the database and cannot tell you how many times all off the information has disappeared or been deleted.

Should I continue letting the users use the current database and build a new one, obeying the Access Ten Commandments? I am open to any suggestions.

Thank you for your time and consideration. Unfortunately, hiring someone to do it for me is not an option. I can always try and provide more information upon request.

It seems sometimes you carry passengers for some reason and sometimes you carry out operations that do not include pasengers. So you could have helicopters that do cost reimbursable operations (from the state) and helicopters that do paying customer operations (carrying people from one place to another). So you have 2 kinds of flight operation types. Is that correct?

Also, do the rates charged by each helicopter ever change, or do they always stay the same?

 

[LarryE]

Can a single helicopter go on more than one flight operation in a single day?

 

[J.Burt]

Passengers are for documentation purposes only and are either classified as a medical transport, trauma transport or a rescue. These are a public service and the passengers are not billed. We bill government agencies, mostly for Wildlands fire responses but occasionally for other flights such as watershed or any ohv related flight.

We will bill based off of a federally determined rate for a model of helicopter, state rate, or local county rate.

We typically run 1000-1200 flights a year, and the helicopter used is based off mission. For law enforcement missions we mostly use the type 1 Bell 206, rescues normally type 2 medium UH1 205 or 412, Fires type 1 UH 60's, but there are exceptions. We use the best tool for mission, so we may use the UH 60 to insert a large SWAT Team in a remote setting, or the 206 to map the perimeter of a fire, and so on. I'll try and attach a zip of the database. It's a mess though, just a warning.

 

[J.Burt]

By chance how big can the zip file be? I have down to 16mb, but obviously too big. Is it best to just delete a bunch of records? Still seems to big even doing that. I can't express enough gratitude for the help and offers to help everyone has given me. Thank you.

 

[LarryE]

Passengers are for documentation purposes only and are either classified as a medical transport, trauma transport or a rescue. These are a public service and the passengers are not billed. We bill government agencies, mostly for Wildlands fire responses but occasionally for other flights such as watershed or any ohv related flight.

We will bill based off of a federally determined rate for a model of helicopter, state rate, or local county rate.

We typically run 1000-1200 flights a year, and the helicopter used is based off mission. For law enforcement missions we mostly use the type 1 Bell 206, rescues normally type 2 medium UH1 205 or 412, Fires type 1 UH 60's, but there are exceptions. We use the best tool for mission, so we may use the UH 60 to insert a large SWAT Team in a remote setting, or the 206 to map the perimeter of a fire, and so on. I'll try and attach a zip of the database. It's a mess though, just a warning.

You already said it was a mess. I think we understand that. It appears the database was meant to be used as you would a spreadsheet, but that is not going to work obviously. It appears you have:

1. Multiple aircraft
2. That are used for multiple purposes on multiple dates
3. Staffed by multiple employees for various reasons (Pilot, Co-pilot, Medic, engineers etc.)
4. That may have one or more pasengers on board for various reasons.

Sound correct? Now ACCESS should not be used to store calculated values, so reimbursements will be calculated on forms and reports only based upon the time differences in hours and minutes between mission start time and end time X the going rate for each helicopter.

This way, flight times can also be calulated for each crew member on each operation mission. These times can then be totalled by any purpose you desire. Crew members times could also be calculated by what purpose they served on any mission. Maybe sometimes an employee serves as a pilot or medic or engineer of some kind. I don't know if that's the case, but possible.

 

[J.Burt]

That sounds right to me.

 

[LarryE]

OK. I have been working on the following table and relationship structure:

I may not have all the fields you need in all the tables, but you can see the relationships. ACCESS uses Primary keys and Foreign keys to keep track of which field in which table is related. For example, the HelicopterID field in TblHelicopters is also a Foreign key in TblFlightOperations.

 

[LarryE]

By chance how big can the zip file be? I have down to 16mb, but obviously too big. Is it best to just delete a bunch of records? Still seems to big even doing that. I can't express enough gratitude for the help and offers to help everyone has given me. Thank you.

The forms and queries and reports are not important. Import just the tables into a new database and attach that file.

 

[J.Burt]

OK. I have been working on the following table and relationship structure:

For clarification, tblCrewActivity will assign a Primary Key number for each entry? CrewID and OperationID are both foreign keys, so for example, on a given operation, tblCrewActivity would produce a unique number for each entry, each employee would have their unique ID, but the Operation ID would stay the same for the crew associated with that operation?

Thinking of how I would continue from here, the crew activities are different then say the pilots activities or medic activities. Would it be correct to create an activities table and then designate each as associated with a pilot, crew chief, or medic. Some overlap and others are specific to a job function. I would then need a job function table to associate each. Maybe Im just lost in the weeds.

Thank you for the post, helped me make sense of things a bit.

 

[LarryE]

J.Burt:
For clarification, tblCrewActivity will assign a Primary Key number for each entry? CrewID and OperationID are both foreign keys, so for example, on a given operation, tblCrewActivity would produce a unique number for each entry, each employee would have their unique ID, but the Operation ID would stay the same for the crew associated with that operation?
Yes.
Thinking of how I would continue from here, the crew activities are different then say the pilots activities or medic activities. Would it be correct to create an activities table and then designate each as associated with a pilot, crew chief, or medic. Some overlap and others are specific to a job function. I would then need a job function table to associate each. Maybe Im just lost in the weeds.
No. We don't need a separate activities table or job function table. These are entered in TblCrewActivity via a form that allows you to either enter the activity for each crew member OR choose from a drop-down combo box list.

As a general rule, I only create separate tables for data we are tracking. We are tracking Helicopters, flight operations, crew activities on each flight and passengers (if any) on each flight. We are also tracking flight hours for each operation for each helicopter. From those hours, we can calculate reimbursable hours and amounts as well as flight hours for each each employee on each flight for each helicopter.
The trick here is if an operation spans two different days, say from 4/15/2023 at 13:00 and ending 4/16/2023 at 13:00. That toal is 24 hours, but ACCESS has specific rules for dates and times. I need to investigate this.

I am working on input forms that will show exactly what I mean. I will post a picture of the forms later today. I am in Indio CA, so our time should be the same.

 

[NauticalGent]

Should I continue letting the users use the current database and build a new one, obeying the Access Ten Commandments? I am open to any suggestions.

I have been silently following this thread and since I cannot improve upon the advice given, I will not add to it. However, I will say that I was once in your shoes with a monster I did not create and I went "Scorched Earth" on it and decided to build it from scratch.

Bad Idea...

Unfortunately, I did not find that article until after it was too late but having experienced it, I can tell you Joel Spolsky was spot on.

Reminds me of something I read a long time ago: Wisdom is the knowledge you gain after it is too late to use it.

 

[J.Burt]

No. We don't need a separate activities table or job function table. These are entered in TblCrewActivity via a form that allows you to either enter the activity for each crew member OR choose from a drop-down combo box list.

Can you explain this? For a given rescue or call a crew member may have multiple activities that we keep track of, ie. Hoist operator, hover load operator, NVG's, ALS Assist etc. I'm not understanding how this will track these for each crew member without having a multivalued field.

 

[LarryE]

Can you explain this? For a given rescue or call a crew member may have multiple activities that we keep track of, ie. Hoist operator, hover load operator, NVG's, ALS Assist etc. I'm not understanding how this will track these for each crew member without having a multivalued field.

Each flight operation may have as many crew members as necessary. Each crew member can perform many activities. This is possible because each activity is a seperate activity comprised of any crew member performing any activity. So you can have any employee crew member acting as Medic or engineer or winch operator or all of them. I am using a combo box control on the activity form I am building that allows any selected crew member to perform any activity. The crew member selection is taken from the employee table, but the activity each crew member performs on the flight is also a combo box control that allows the user to input an activity OR select a previously defined activity. So yes, these fields may be considered "multi-value" although that term may not be what you are thinking of.