Tracking several years of data

[MTuminello001]

Hi Guys,

I am just getting back into the Access game since i've started a new role in finance which requires it of me. I have a relatively basic question. I'm just looking for a best practice from someone with the experience. I am looking to track the salary information for my small firm over a number of years. Does it make sense to create a table with multiple fields that would be structured like this (2011401(k), 2011Medical, 2011 LifeInsurance, 2012401(k), 2012Medical, 2012LifeInsurance, etc) or is there a better way to tackle this? Any help would be greatly appreciated. Thanks!

 

[plog]

Table and field names should not hold specific data. You are trying to put Year information into a field name, don't. You should create a field for that data, something called 'BenefitYear'. Your structure would then look like this:

401k, Medical, LifeInsurance, BenefitYear

It may even be the case that 401k, Medical and LifeInsurance columns become their own rows replaced with a 'BenefitType' field like so:

BenefitType, BenefitYear, Amount

Do 401k, Medical and Life Insurance all contain currency values?

 

[MTuminello001]

Plog,

Thanks for your reply. Yes, all of these different elements of pay will have their own values for years 2011 and 2012. In the end, this is a report that will need to be given to employees showing these separate elements across the two years. I am having trouble with the design aspect of the tables. An added wrinkle is that I need to restrict access to the salary and bonus aspects to one person. So would I need to create a separate table for that? It seems to be bad table design... I appreciate the help.

 

[plog]

An added wrinkle is that I need to restrict access to the salary and bonus aspects to one person.

Do you mean only 1 person can see 1 record (as in only the person can see their own data)? Or do you literally mean 1 person?

How many people will be using this? How savy are they?

 

[MTuminello001]

Sorry for the confusion. The salary and bonus data for all employees will be updated by one person only (due to the nature of the data as classified). I will be in charge of updating the other less-confidential data for the employees(employee benefits). I need to create a report showing all of this data in the end together. So I suppose I have two main issues:

1. Table structure - Do I need separate tables for the two sets of data (classified v non-classified)? If I do that, would I be best off with a parent table with only EmployeeID and Name fields and then two other tables for the specific data?
2. Splitting the DB

Your help is greatly appreciated

 

[plog]

I need to create a report showing all of this data in the end together.

Now I'm more confused. You don't want to be able to see the classified data entered, but you do need to see it output?

 

[RainLover]

How can you not see the Data when you are the programmer.

You will have full access to the Tables.

Splitting the DB with a Password on the Back End will help to keep others out but you must be in possession of the Password or you won't be able to make Edits/Improvements without it.

 

[MTuminello001]

Sorry, I will try and lay out the project for you.

End Result: Report which shows employee's total compensation for 2011 and 2012 (separated by salary, bonus, 401(k) and benefits)

Data Entry: I will be inputting the benefits and 401(k) data and HR will input the salary and bonus information. The salary and bonus information is not to be seen by anybody except the HR rep inputting the data.

My thoughts: I was attempting to create a parent table with EmployeeID as an AutoNumber and EmployeeName. Then I was going to create two separate tables, one with the confidential info (salary and bonus) and the other with the 401(k) and benefits info. I was then hoping to split up the database and password protect the database which contains the confidential info. (We don't need super tight security, this is a small firm). Let me know if you need any more details. Thanks again

 

[MTuminello001]

I understand RainLover. This was a request from my CFO, so I am seeing if there is a way for me to make this happen. I appreciate the help.

 

[RainLover]

You have two issues.

One is Security. You should start a new thread for advise on this.

The other is your Table design. You will need about 5 or 6 Tables to cover what you have mentioned.

I could help with this but not just now due to the Time. 1:30 AM here. Post back if you want some help. However I would like a Demo DB from you with the Tables as you see it.

Please post in Access 2003.

 

[RainLover]

I understand RainLover. This was a request from my CFO, so I am seeing if there is a way for me to make this happen. I appreciate the help.

Your CFO needs to understand the simple reality of the matter. What else can I say.

 

[MTuminello001]

RainLover,

So there is no way for:
One person to update one table and then another person to update the second table and run the consolidated report which pulls data from them both? What your saying is that one person needs to be in control of the whole process? Thanks again

 

[RainLover]

RainLover,

So there is no way for:
One person to update one table and then another person to update the second table and run the consolidated report which pulls data from them both? What your saying is that one person needs to be in control of the whole process? Thanks again

No, I never said that at all.

What I am saying is that you as Programmer will always have access to all the information in the Data Base.

You can lock others out but not yourself.

 

[plog]

I'm still not certain if this report you are to run is at the employee level or grouped up somehow. If its at the employee level, you cannot run the report without having access to employee level data. If you are grouping this data up somehow (by department, by company, by gender) then it is possible to restrict the employee level data to just one person who isn't you.

So, what level is this report being run? How is it being grouped? If at all.

 

[MTuminello001]

The report will be run on an individual employee level. So it will be relatively simple report (we are small), showing the 2011 salary, 2011 bonus, 2011 401(k), 2011 health benefits and then the same for 2012, with a total compensation at the bottom for each of the two years. Hope that helps

 

[plog]

It helps in that it explains that it is impossible.

You are saying that you need to hide the employee level data, but then you turn around and say you need to run a report on that exact same data. How do you propose to run a report on data you aren't allowed to see?

 

[RainLover]

There is no need to put confidential Data/Fields into a separate table.

You could use a Username Password situation to decide wether or not to display these fields.

There is a lot of different ways to create this. Just do a search.

On the other hand you could write two Front Ends. One that shows the confidential Data and another that does not. They would both be linked to the same back end.

 

[CBenfer]

I'm no expert here, but here are my thoughts on the security issue:

Set up your DB so that the table data is filled in via forms - probably one for confidential info and one for non-confidential info. But all of the data should be in one place. Also set up your report.

Make sure security is set up to prevent access to the tables directly.

Create three accounts. You will be the holder of the first account with "user only" access - basically access to the non-confidential form and whatever else you want.

The second account would be for HR - they should be able to use the confidential form info and view the report.

The third account would be an "admin" or "superuser" account. It would be able to get into everything, but not normally be used to prevent you from seeing data that you shouldn't.

This obviously doesn't prevent you from intentionally digging up any data, but it does prevent you from accidentally opening something you don't need to see. If your CFO or HR or whoever trusts you that should mostly take care of the issue. If not, there are other things you could do (some sort of encryption on the raw data with the HR person having the key comes to mind) but that's getting excessive for the small project you're working on.