Access Query for Prorating?

[jaswmil]

I have a weird situation that I hope someone can help me make sense of.
I am working with PeopleSoft HR tables. Unfortunatley, the powers
that be when they implemented PS (I wasn't here), decided to have two
trees for department IDs. So, HR has one department ID structure and
Finance has a different department ID structure. For example, my HR
DEPT_ID might be HRSERVICES but the Fiance DEPT_ID is H0101235.

SO, now I am trying to conver Finance DeptID to HR DeptID so that I can
compare budgeted FTE to actual FTE. After MUCH data churning and back
doors, I was able to do so. However, now I have one more problem. The
relationship between HR and Finance dept ID is not one-to-one. For
example, in the above situation, HRSERVICES may have three dept IDs.
So, after I have my conversion done, I get the budgeted FTE (budgeted
comes from the financial side) FTE duplicated three times... Ugh! So,
to get around this, I decided that I would just evenly prorate over
three, or four, or two, or however many I need to.

Does anyone have ANY idea on what I can do to solve this issue? Any
suggestions would be greatly appreciated!

 

[KARL DEWEY]

Prorating is not just a matter of dividing the total by the number of
departments that are to use the funds. You probably also need to use some
form of weighted-prorating. Such as department A has 3 employees, department
B has 15, and department C has 150 employees. The budget would not be split
equally between the three departments.

You should have built some kind of cross reference table like this –
Human Resources Finance
HRSERVICES H0101235
DRAFTING E0101333
ENGR E0101301
ENGR E0101303
ENGR E0101303

Add a field to the table for prorating factor such as employees (Labor),
floor space (Utilities), vehicles (Fuel, repair, etc), etc. based on budget
line item.

You really need to budget to the lowest nominator.