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