 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / December 2007Need assistance-Don't know if I set up too many tables
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. 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.  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com >I need to be able to calculate real time vacation balances. > [quoted text clipped - 20 lines] > Would like to set this up so that each employee can access their own > information. 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? > 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 [quoted text clipped - 24 lines] > > Would like to set this up so that each employee can access their own > > information. > 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 [quoted text clipped - 3 lines] > > 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. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.