Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Posts
    11

    Unanswered: A simple table design question

    Hi everybody,

    I have an employees table (names, etc.), a work area table (places) and a work shift table (AM/PM/Overnight, etc.). I want to be able to assign a person from the employees list as supervisor of each shift in each work area. I can see how to assign someone as supervisor of a work area or a shift but I dont see how to assign one to the workarea/shift combination. Can you help me with the general concept Im missing?

    Thanks for helping a newbie
    Bob

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Try these table schemas.

    1) AreaShift.gif
    This assumed that a person is always responsible for that Area/Shift combination. This will work if you don't need to keep a history of who was responsible on a given date. If you need to schedule it daily, add a date field to tblAreaShift.

    2) AreaShift2.gif
    This is another way of doing it that also keeps track of who was assigned and when but requires more work to ensure that someone doesn't get assigned to the more than one area for the same shift.

    Hope this helps!
    Attached Thumbnails Attached Thumbnails AreaShift.gif   AreaShift2.gif  
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    May 2004
    Posts
    11

    Thanks

    Cosmos75

    Thanks Bro, I think I'll get that raise after all!

    Bob

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by bastille
    Cosmos75

    Thanks Bro, I think I'll get that raise after all!

    Bob
    No problem.

    So, when do I get my cut of the raise?


    Let me know if either of those table designs works for you or even if they don't and I'll help as much as I can.
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    May 2004
    Posts
    11

    About that raise...

    Hi Cosmos,

    I hope you don't mind me following up with you on that advice you gave me on assigning supervisors to employee shifts but I've run into a snag. I used your first suggestion about how to design the shift/workarea table (named Supervisors) and it looks good (attached). I think the problem is in the relationship between the supervisor field that references back to the employees table. I've seen it called "cross referencing foreign keys".

    In tableupervisors, fieldupervisor I used the lookup wizard, selected table:Employees_List fields:L_Name and F_Name. It does create a drop down list to assign a supervisor to the record, but when I create a report or form it shows the areas that employee supervises instead of the name of their supervisor.

    The goal I'm going for here is just to be able to show each employees supervisor on a Basic Employee Data form.

    Adding this little field has become more frustrating than learning user level security. Can you see what I'm doing wrong here?

    Thanks,
    Bob
    Attached Thumbnails Attached Thumbnails supervisor relationships.GIF  

  6. #6
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by bastille
    Hi Cosmos,

    I hope you don't mind me following up with you on that advice you gave me on assigning supervisors to employee shifts but I've run into a snag.
    Not a problem, that's what I am here for, to help and be helped!

    Quote Originally Posted by bastille
    I used your first suggestion about how to design the shift/workarea table (named Supervisors) and it looks good (attached). I think the problem is in the relationship between the supervisor field that references back to the employees table. I've seen it called "cross referencing foreign keys".
    Why don't we start at the beginning...

    Explain to me what data all the tables contain and give me examples of what is a Line, Shift, Work Area and so on and how they are related. There may be something I am missing as I didn't have time to look over you tables and what they mean, merely to answer your question. Someone once told me - "give them what they need, not what they want" and I may have missed the boat on that one by not asking more questions (or at least the right questions).

    It usually pays to spend a much time as need upfront thinking about your table design as everything else will very much depend on it - ease of creating forms, queries and so on. You can blow through a table design that "works" but then when you didn't account for something or need to do something in particular with the data - that quick table design can cost you WAY more time in designing forms/queries/reports that work they way you want them to or RE-structuring the tables AFTER data has been entered. Not good!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  7. #7
    Join Date
    May 2004
    Posts
    11

    It was right in front of me...

    Hi Cosmos,

    Thanks a ton for the offer but it seems the answer was right in front of me all along. I re-read my Access for dummies book (several times) and saw that the Northwind sample database does just what I want. It puts a supervisors field in the employee table by using a query of the employees table. Not that I understand it 100% yet, but it seems to work just fine.

    If you ever need advice from an expert in bar/restaurant management, I'd be happy to return the favor.

    Bob

  8. #8
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by bastille
    Thanks a ton for the offer but it seems the answer was right in front of me all along. I re-read my Access for dummies book (several times) and saw that the Northwind sample database does just what I want. It puts a supervisors field in the employee table by using a query of the employees table. Not that I understand it 100% yet, but it seems to work just fine.
    Glad to hear you found a solution!

    Quote Originally Posted by bastille
    If you ever need advice from an expert in bar/restaurant management, I'd be happy to return the favor.

    Bob
    Thank you, Bob!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

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