Calculation Method

[aussieaj]

Hi everyone,

I have a database that records the amount of winches a crewmember may do on a helicopter on a given flight.

Within a separate table/layout for staff members, a calculation creates the sum of all the winches that each staff member does from the flight details they are listed as a winch operator for.

Is this an inefficient way of recording this? Would I be better off inserting the information straight from the flight details into the staff table?

Cheers

Andrew

[bcooney]

My instincts tell me that you've got it setup correctly, but to confirm:

1. Staff are assigned as Crew on a flight, each with a job role? So, Crew is a child table of Flight. It could be a portal on the Flight form, and you'd enter a StaffID and RoleID?

Table of Roles, with a record like:

ID Role

100 Winch Operator

I'm confused btw counting how many times a Staff member was assigned to be a Winch Operator on a flight and your statement, "records the amount of winches a crewmember may do." That seems to indicate that there is a need for a Jobs table as a child to Flight (FlightID, JobTypeID and StaffID), and you'd record the Jobs performed (using a JobType table as your value list source). Then, you'd count JobTypeIDs for a staff member, not their role on the flight.

[aussieaj]

Hi Bcooney,

You are spot on, I have a flight table with a portal to the flight crew table. You select your staff and their job role through this portal.

I see the part that has caused you confusion, and I initially did set up this way to record the amount of times they were listed as a winch operator. The only problem is on any given flight they may conduct several winches, so I need to record the total amount they have done instead of just one instance.

So when they add in their winch I have a script that asks for how many winches they did, and then the script determines whether it was a Type A or B winch dependant on whether it was a big or small aircraft used. The number of winches are then recorded against the crewmembers name in the flight crew table. The field in the staff listing/skill currency then goes in and does a calculation sum of each time that crewmember has a type A or B winch and provides a summary of that data. I just didn't know if I was duplicating it by recording the info in both the flight crew and the staff currency sections but I don't see any real way around it.

Thanks for your reply, hopefully what I wrote makes sense?!

[bcooney]

I don't understand your data; I have no clue what a winch is.

So, how come you do not have a jobs table as a child to a Flight? Summing that would answer "how many winches they did."

[Lee Smith]

Hi Barbara,

I don't understand your data; I have no clue what a winch is.

I'm sure that you know what a winch is. If you ever seen a man pull an engine from a car, or a Helicopter rescue a person off the side of a mountain (or at Sea), or lift a large piece of equipment to be transported to a different location, what they are using is a winch to pull in the lines.

What I'm understanding from the question is that they have at least two classes of winches, A and B, possibly one is for a heavy load, and the other for a lighter ones, maybe they are even used together at times, and when they used one or the other or both, he wants to record which one the crew operator used.

If so, why isn't the two types of winches a selection in the same field (checkbox comes to mind, or drop down list), and then you could do a report off of this?

Lee

Edited October 23, 2010 by Guest

[aussieaj]

Hi Lee & Barbara,

Thanks for your input, and Lee you are right about we have slower, shorter winches and faster, higher & heavier winch types. Sorry Barbara I should have explained this a little.

I will create a seperate table for the winches, I think I'm trying to shortcut it by just adding the fields into the existing crew list table when I really should have a seperate table to record the winches, and then create a summary of that.

Thank you both, I'll give it a go and report back!

Cheers

Andrew

[bcooney]

That sounds like what I've suggested: you need a jobs table as a child to a flight. Be sure to "code" jobtypes so that your report is accurate.

[aussieaj]

Thanks Barabara,

It's working a lot more now with the seperate table. What exactly do you mean by 'code' the jobtypes?

Cheers

Andrew

[bcooney]

I'd have a table, "JobTypes," ID and Name, and I'd use that to populate the Jobs table. Each Job has a JobType, but store the JobTypeID. This way you can report accurately by JobType, if required. "Sum of all the winches that each staff member does" sounds like a count by JobTypeID.

Also, you can create a portal on the JobType form to Jobs by JobTypeID and see all the Jobs of that type.