Getting to the end

[tempk]

Hello everybody,
I am over my head with this problem.

I have a table that has details of staff at my workplace. I am using access to calculate the impact of staff movement on our budgeted salary. Thus, I have set up a system in which managers can use some forms to transfer, reclassify or add new staff. My table has flags (Reclass/New/Transfer) and also the month the changes will take place (NewMonth,ReclassMonth,TransferMonth).

From this table, I would want to be able to get a report that shows the base salary at the beginning of the year and each month's salary, taxes and bonuses. (Denormalized structure)

Initially, I made separate queries to get the incremental salary, taxes and bonuses and then use another query to sum each component up and added it to a base salary from another table(this table is maintained by the HR people).
For example, 1 query to calculate the new staff costs, transfer staff costs and reclassified staff costs. Then from this query, I break it up into a denormalized structure via a sum of a dlookup on incremental salary/tax (NewMonth etc..) This would be another 4 queries. And at the end, I have one query that adds up the results of the previous 4 queries (BaseSalary + Incremental New Staff Salary + Incremental Reclass Salary + Incremental Transfer In Salary + Decremental Transfer Out Salary). In the end, I ended up with almost 50 queries!

I find it cumbersome and set on redoing the whole process based on the same logic. By using nested iifs and dlookups, I can get to my final answer
with only 3 queries but the dlookups takes up too much time.

I am hoping for a faster way to get my final results without the large number of queries!

Would anyone have any idea of what I am talking about? Haha.
Please do help!

Rgds,
k.

 

[tempk]

Hi Pat,
you are always enlighten those of us who are lost.

I guess the whole design had flaws to begin with. I just kept going and going and finally ended with a huge crazy pain in the ***. Haha.

Perhaps I have to relook at the whole thing and redesign it.

 

[tempk]

I managed to normalise my initial table into 4 tables each for reclass, new adds, transfer in and transfer out (each linked to my StaffTable by fkStaffID). e.g.TrOutID fkID TrOutMonth TrOutStaff(number of staff transferred out)

tblStaff structure: PkID Dept JobDescription CorporateLevel ExistingStaff
tblSalaryBase: CorpID CorporateLevel Salary

Staff costs are calculated using a select query where TrOutSalary: [tblSalaryBase]![Salary] * [TrOutStaff]. tblSalaryBase is linked to tblStaffTrOut by an inner join on corporate level.

I also have a table with the dept's salary at beginning of year where I want to add the incremental salary to. But I am having some problems here...I need to be able to see the effect on the base salary across the whole year in a denormalized structure.

If I use a cross tab query on my staff cost, I get:
Dept [1/2/3] (depending on month) and Salary as Value

However it is not possible to add BaseSalary to [1/2/3] if the columns don't exist.

I tried using Dlookup:
Mth2: [BaseSalary] + DLookup([NSalary],"qryStaffNewSalary",[NewMonth]=2)
However I would run into nulls or getting an error if qryStaffNewSalary does not have the dept's record.

I tried using subquery such as:
Mth2: [BaseSalary] + (SELECT [NSalary] FROM qryStaffNewSalary INNER JOIN qryStaffNewSalary ON tblDeptSalary.Dept = qryStaffNewSalary.Dept
WHERE (((qryStaffNewSalary.NewMonth)=2)))
but I get a syntax error instead.

I still think that such calculations should be done in excel since my boss wants to see it in a denormalized structure!

Help???

 

[tempk]

yep, i already have lookup tables for departments and also rates.

so i should have only one table with all the people's information inside and another table linked to it which is for storing the transactions? okay, i get understand what i need to do: using the transaction table, there is a field which indicates the transaction type?

for example:
pkTransactionID fkStaffID fkTransactionID MonthofTransaction

Am I right? So to calculate what are the total costs for say, new staff, I only need to use a query to show a particular transactionID?

I can understand how this is much much better than four tables! lol, even though the idea behind the tables is to store the different transactions.

Pat, you're amazing! lol

 

[tempk]

Question though, new staff would be appended on to the current staff table and half a corresponding record in the transaction table, am I right? How about transferring departments? How should I indicate which department the particular staff is being transferred to? I came out with the different tables because of these issues..

Gotta try it out in the office tomorrow. Sunday's are best left to laze and not think! lol

 

[tempk]

Okay. Thanks for the advice, Pat!