Results 1 to 2 of 2
  1. #1
    Helystra is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Posts
    35

    tracking multiple wage types

    I have been to Roger's blog and have been making my way through a rather complicated database build. Using the Entity Diagramming, I have managed to sort out all but one last issue with my database design. I will copy my narrative here and hopefully someone can help me sort out the issue i'm having with joining multiple wage types, job titles, schools and budget codes and employees.



    FOOD SERVICE DATABASE ENTITY DIAGRAMMING NARRATIVE Entities and Attributes

    Employees (empID, lastname, firstname, DoB, HireDate, status and staff type)
    Schools (campusID, School name, type, address, city, state, zip, main phone, cafeteria phone, and fax)
    Wages (employee-foreign key, wagetype, regularrate, overtimerate)
    budget code (name, fund, function, object, OTobject, sub object, OTsub object, org, year)
    job title (title, hours, jobcode)

    All tables also have an autonumber field as their PK.

    For Assignments I need to join the following information: employee, school, job title, wages, and budget code

    Each kitchen, substitute, central office and warehouse stafftype employee may have multiple wagetypes (contract, differential, sub, lead and assistant - these are determined by the job title). Contract and Differential wagetype rates are different for all but the substitute employees. However, each other wagetype will be the same across multiple employees. (subs and assistants are all X reg and Y OT, Leads are all A reg and B OT)

    A school has multiple job titles and a vacancy or employee for each job title. A job title may be assigned multiple times to the same school as well as to multiple schools. An employee may be assigned to multiple job titles at the same school or across multiple schools. A budget code is determined by wage type and may be assigned to multiple employees, job titles and schools. There is one budget code for regular pay and one for overtime pay. the only difference between them is the Object and the Sub object.


    And this is where I'm stuck...I'm thinking that I need to join up the budget codes, wages and job titles by wagetype since that seems to be the common factor between the three of them. However, I have been told that once I relate a table to another I shouldn't do it again directly or indirectly. And the employees are already related to the wages so wouldn't that indirectly relate the employees again once I put it all together in tblassignments? Also, I think it's rather redundant to store so many different records to track the same pay rate for substitutes, assistants and leads and I would like to be able to just have the set rate for assistants pull up when I assign an assistant job title to a school regardless of what employee is assigned. I know there must be a way, but for the life of me I can't see it.

    Ideas?

  2. #2
    Helystra is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Posts
    35
    New thought. If I don't relate the employee to the wages and just have a list of wage combinations. I don't have to store duplicate rates...like 150 different x reg and y OT combinations.

    Then if I relate the budget code to the job title since that is what it is ultimately dependent on that leaves the wages/rates as a stand alone table that can then be related to Assignments independently.

    Then in the assignments table I have: school, job title, employee and wage (budget code was previously related to job title outside the assignment tbl).

    That way for all the lead cooks I can just pull up the one wage rate combination and the other for assistants.

    Does this sound like it will work?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run multiple reports for multiple asset types
    By cphelps in forum Reports
    Replies: 1
    Last Post: 09-08-2013, 09:01 PM
  2. Replies: 3
    Last Post: 07-25-2013, 11:35 PM
  3. Replies: 7
    Last Post: 10-25-2011, 08:32 PM
  4. Replies: 1
    Last Post: 08-09-2011, 11:13 AM
  5. Table design to Track Multiple Client Types
    By TannerT in forum Database Design
    Replies: 6
    Last Post: 06-02-2010, 08:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums