Promotions and Addition

[tempk]

Hi all,
I have a table with the number of existing staff per department separated by their corporate level. I am able to use a query to display the number of staff per month(Mth1=ExistingStaff..so on).

Since there will be staff changes such as promotions or new staff additions, I need to cater for that. Thus, I created separate tables for each case.(tblNewStaff & tblReclassStaff) The idea is to use a form to allow the users to choose which staff to promote (reclassification in corporate level)where the user just has to specify which month the change is in. A separate form is used to add new staff.

However, I am having problems trying to figure out how to make changes to the Number of Staff per month after such changes. New staff are added as new records while reclassifications will alter the current numbers. Both has been dogging me.

Is there a way to dynamically generate this through a query? Or is there a better way to go about doing this? The numbers collected will be used to calculate incremental salary etc...

Thanks for any help! My brain is fried after trying to solve this problem the past two days...

 

[DJN]

Why have new tables? Just add a flag field. 1 for new staff, 2 for reclassified staff. You can add additional flags for different reasons. Easier to build a query as well. Just my suggestion.

 

[tempk]

May I know what is a flag shield and how do I implement it?

Still new to access and learning much here!

Tables are used so that I can use the data collected to calculate the eventual increase in salary due to the new staff or promotions. But if I can do it simply with a flag shield (whatever that is) I'll be happy.

 

[tempk]

Okay..figured out how to use flag field for adding new staff(without relying on another table) but I'm still trying to figure out a way for reclassification of staff.

Because I need to be able show the user which month the changes has taken place and also to calculate total headcount for that month in the format below:

Dept Designation Level ExistingStaff Mth1..Mth12 (where mth1 = existingstaff, unless NewStaff checkbox is ticked then Mth1=number of newstaff+existing staff)

For example employee A who currently has a Lvl 1 status is promoted to Lvl 2 in July, I would want the change to reflected such that Lvl 1 in july shows zero.

The coffee I have drank so far has yet to prove effective in keeping me awake..haha. Maybe I am approaching this problem from a wrong angle.