Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opinions please: one table or more? (Office xp)

    Hi,
    I haven't used Access in years so I've forgotten a few things. <img src=/S/frown.gif border=0 alt=frown width=15 height=15> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I need to make a simple (I think) Access database that keeps track of employee benefits. What the managers want to know is what are the employee balances for vacation, sick, personal, flex time,etc.... How much time was taken, how much is left. etc. Vacation benefits also depend on length of employment also.

    I know that I will need a employee table but I was wondering if I need a table for each benefit as well or if I should put all the benefits in the same table.

    I'l have to design a form that I can use to input the information as well. I'll also have to brush up on how to do the calculations. <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    What do you think? Should I have a table for each benefit or not?

    Thanks,
    Louise

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opinions please: one table or more? (Office xp)

    Hi Louise,

    a) Do you want to keep a history of all mutations, or are you interested in the current balance only (how much has een taken and how much is left)?
    [img]/forums/images/smilies/cool.gif[/img] Do you need to be able to add the different benefits together easily, or is that not important?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opinions please: one table or more? (Office xp)

    Hi Hans!
    1. I don't know what you mean by "mutations". They are mostly interested in balances but I think that should a questions arise, I should be able to give specific dates if necessary.
    2. If I understand your questions correctly, I won't be adding the benefits together. I would be nice to have them on the same form however.

    I'm picturing a form where I would have the employee name and date of hire. Then it would also show the balances of each benefit or I was also thinking of having "tabs" for each benefit which would show the balance and (I forget what you call it) a picture of the database with the activity for that benefit. I hope you understand what I'm trying to say?

    Thanks again!
    Louise

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Opinions please: one table or more? (Office xp)

    I would probably use several tables:
    1. <LI>tblEmployees - general info about employees:
      EmployeeID (Primary Key), LastName, FirstName, SSN, ...
      <LI>tblBenefits - general info about benefits:
      BenefitID (PK), Description, ...
      <LI>tblBenefitsCredit - how much is available; there is one record per employee per benefit per time period (for example, calendar year):
      EmployeeID, BenefitID, Period (composite PK on these three fields), Hours (or Days, or whatever), ...
      <LI>tblBenefitsDebit - how much is taken; there is one record per employee per benefit per "event", perhaps by specifying the starting date:
      EmployeeID, BenefitID, StartDate (composite PK on these three fields), Hours (or Days, or whatever), ...
    You can use queries to compute the balance per employee per benefit; if the calculations become too slow, you might consider storing the balances in a separate table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •