vb.net data: I am developing an application in VB.NET to produce some reports. The
back-end DB is an MS Access db. I have a table with the details of
employees, a table with several projects, and a table with details for
a specific month for each project.

Due to the nature of the paying scheme of the employees, their pay rate
does not change often. It is based on how many years they have been
working and changes every three years. As a result the details for the
employees also contain their pay rate.

The problem I am facing is that when an employee's pay rate changes
after say three years, changing this value in his/hers details, will
also change the ratre used in all the projects the particular employee
has worked in to this point. Therefore the costing history of the
projects is lost.

Any ideas of how to go about solving this problem.

One way I have thought of, is to have a seperate table with pay rates
which will have the effective date for the salary of each employee as
well as the employee's id and rate. Then I can choose the appropriate
salary based on the month of the project and the effective date of the
salary.

Does this solution make any sense? Does anyone have any better ideas?

Thanks,

Lefteris

Your proposed solution is a good one. his gets you past the redundancy of
putting an employee's pay rate with each and every project. You can the
write and SQL query that will give you the pay rates in effect for any point
in time and use that when you calculate project costs for a given point in
time.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
[quoted text, click to view]
I am developing an application in VB.NET to produce some reports. The
back-end DB is an MS Access db. I have a table with the details of
employees, a table with several projects, and a table with details for
a specific month for each project.

Due to the nature of the paying scheme of the employees, their pay rate
does not change often. It is based on how many years they have been
working and changes every three years. As a result the details for the
employees also contain their pay rate.

The problem I am facing is that when an employee's pay rate changes
after say three years, changing this value in his/hers details, will
also change the ratre used in all the projects the particular employee
has worked in to this point. Therefore the costing history of the
projects is lost.

Any ideas of how to go about solving this problem.

One way I have thought of, is to have a seperate table with pay rates
which will have the effective date for the salary of each employee as
well as the employee's id and rate. Then I can choose the appropriate
salary based on the month of the project and the effective date of the
salary.

Does this solution make any sense? Does anyone have any better ideas?

Thanks,

Lefteris

Hi Tom,

and thanks for your reply. In the meanwhile, I thought of another way.
This would be to use the pay rate of the employee (as this comes from
the employee details) as a proposed value in the form where the user
enters the monthly project details, but then store it as a seperate
field in the details for the month for the project. This way I have
more flexibility. Also, this in combination with my earlier suggestion,
will almost always provide the user with the correct proposed rate.

Let me know what you think

Lefteris

Almost always? Why take the risk (and commensurate hassle) when you can
just have a normalized schema? I'd go with your original thoughts. If you
have problems with writing the SQL to retrieve it, post the DLL + sample
data + expected results in the .programming newsgroup. You'll get a very
prompt response.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
[quoted text, click to view]
Hi Tom,

and thanks for your reply. In the meanwhile, I thought of another way.
This would be to use the pay rate of the employee (as this comes from
the employee details) as a proposed value in the form where the user
enters the monthly project details, but then store it as a seperate
field in the details for the month for the project. This way I have
more flexibility. Also, this in combination with my earlier suggestion,
will almost always provide the user with the correct proposed rate.

Let me know what you think

Lefteris

Thanks Tom,

I agree with you in the unnecessary hassle of almost getting it right.

Also thatks for the tip about getting help from the programming
newsgroup, will keep it in mind.

Lefteris