Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Location
    Manila, Philippnes
    Posts
    17

    Unanswered: table structure issue

    i am currently working with a human resource outsourcing company. we deploy contractual employees to different companies.
    we usually use ms excel in keeping records of our employees that are deployed to different companies, now my boss wants me to create a database using ms access. this is my first time in creating database using ms access.

    here are the headers that we used with our excel database:

    - ID number
    - Account number
    - First name
    - Last name
    - Middle initial
    - Start date with the agency
    - Start date with the company
    - Status (active, end contract, resigned, terminated)
    - Previous contract start
    - Previous contract end
    - Present contract start
    - Present contract end
    - Company
    - Outlet location
    - Position
    - Pay class (daily or monthly)
    - Basic Rate
    - Allowance
    - Total Pay
    - Home address
    - Home tel. no.
    - Mobile no.
    - Civil status
    - Date of birth
    - SSN

    what i did was divide the headers into several tables for ms access as follows:

    1st table (Personal Info)

    - ID number
    - First name
    - Last name
    - Middle initial
    - Home address
    - Home tel. no.
    - Mobile no.
    - Civil status
    - Date of birth
    - SSN

    2nd table (Company info)

    - Company ID
    - Company Name
    - Company address
    - Contact number
    - Contact person

    3rd table (Position info)

    - Position ID
    - Start date with the agency
    - Start date with the company
    - Status (active, end contract, resigned, terminated)
    - Previous contract start
    - Previous contract end
    - Present contract start
    - Present contract end
    - Pay class (daily or monthly)
    - Basic Rate
    - Allowance
    - Total Pay

    4th table (Outlet Info)

    - Outlet ID

    5th table (Status info)

    - Status ID

    6th table (Pay class info)

    - Pay class ID

    7th table (Pay class info)

    - Civil status ID

    are these correct? please feel free to comment or suggest. i do apologize for the inconvenience. i am just a newbie and i need to finish thi project. i appreciate your response. thank you in advance.

  2. #2
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    [Position Info].[Previous contract start] and [Position Info][Previous contract end] are not strictly necessary as they are calculated fields i.e. this data can be pulled from the previous record. In fact you'll have null values (takes up uneccessary space) if they're a new employee.

    [Position Info].[Status] is also calculated as it's largely a function of [Present contract end].
    1 "active" can be expressed as Not IsDate([Present contract end])
    2 "end contract" is IsDate([Present contract end])[=True]
    3 "resigned" and "terminated" values should be moved to another field e.g. [Position Info].[Reason for contract end] that can only be assigned when IsDate([Present contract end])

    [Position Info].[Total Pay] is also potentially a calculated field if you also have weekly/monthly payroll records elsewhere.

    In an ideal world all information in calculated fields can be displayed in forms or reports using queries and or a little code. There are a few clues on how to do this in the comments but post a reply if you get in over your head.

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i think MNF is saying something similar, but here it is expressed my way:

    i don't like the 3rd table (position info)

    current position, previous position ...what happens to the pre-previous position and the maybe-future-position data.
    plus you complicate your life when the current position becomes the previous position ...you need to shuffle data around within the table.

    - Position ID
    - ID of the person in this position <<< FK to person
    - Start date with the agency <<< property of the person not the position ???
    - Start date with the company <<< property of a position
    - Status (active, end contract, resigned, terminated) <<< property of a position
    - Contract start <<< property of a position
    - Contract end <<< property of a position
    - Pay class (daily or monthly) <<< property of a position
    - Basic Rate <<< property of a position
    - Allowance <<< property of a position
    - Total Pay <<< calculated value as per MNF ???

    i would probably keep status as you have it, tho MNF's argument seems reasonable.

    looks like your definition of a 'position' is something specific to one person-event (all those dates, pay-levels etc), so i added the person FK.

    izy
    currently using SS 2008R2

Posting Permissions

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