Need assistance-Don't know if I set up too many tables

[NewAccessUser]

I need to be able to calculate real time vacation balances.

I have set up various tables:

1. Employee Listing - This includes Dept, Class, Type, Status, Name
2. Beginning Balances - Balance at beg of 07
3. Vacation Taken - This includes each pay cycle and how many hours were
taken
4. Accrual 1 - Includes both officer and non-officer accrual rate based on
# of yrs.
5. Accrual 2 - Includes both officer and non-officer accrual rate based on
# of yrs.
6. Accrual 3 - Officer rate based on # of years.
7. Accrual 4 - Add'l holiday hours earned based on date of hire.

I need to be able to come up with various reports:
1. Shows balance at any given date.
2. Shows time taken by cycle and time earned YTD with a total available.
3. Be able to split this up by department.

Would like to set this up so that each employee can access their own
information.

 

[Arvin Meyer [MVP]]

Accrual 1 and 2 appear to be duplicates, if not of data, certainly of
purpose. Accrual 3 and 4 can also be combined, but 4 seems to be the result
of a calculation which can be made at any time, and therefore does not need
to be stored.

 

[NewAccessUser]

Accrual 1 and Accrual 2 are separated because it is dependent on the class
which table it will read. Accrual 4 has some specific criteria in that if
you are a new employee at the last quarter, you earn 0 add'l holiday hours.
For this reason I made a separate table.

Can this be handled in an expression?

 

[Arvin Meyer [MVP]]

Accrual 1 and Accrual 2 are separated because it is dependent on the class
which table it will read. Accrual 4 has some specific criteria in that if
you are a new employee at the last quarter, you earn 0 add'l holiday
hours.
For this reason I made a separate table.

Can this be handled in an expression?

Possibly, but the design is definitely wrong. Adding a field in Accrual1 for
the class will differentiate between the 2 types of classes. A simple
expression in a query that looks at the HireDate can compute how many
additional hours to add (or not) Even if there are a dozen different rules,
they can easily be differentiated in a custom function using a Select Case
statement, that can then be used in a query expression.