Results 1 to 3 of 3
  1. #1
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164

    Table Design - Relationship Structure

    Hi,

    I have a DB of around 33 staff members.

    They can all book Holidays for any week within the year, One Employee for example might book off on holiday the 1,2,3,4 January, a while later another employee might want the the same days.

    That it is fine as we allow 2 off at the same time.

    My thinking is that this situation is now a Many to many relationship but I'm not sure of how to structure it?

    One employee may have one to many holidays, An Holiday may have one or many staff in the same holiday dates, months.

    I know this requires a Junction table but what items/fields do I move into it to make this work in a form later (If a query feeds it)

    I want to get this right before we move further.

    Any help would be appreciated.

    Attachment 27782

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,007
    I don't think tMonths table needs to be in the relation.
    access knows what a month is AND it's not a child table so it does not need to be there.
    the tHoliday table just needs a Date field,for the vaca date. Access knows this date is JAN, or FEB.
    remove this table.

  3. #3
    djspod is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Mar 2015
    Posts
    164
    Hi Ranman,

    Thanks for your advice, I get the logic regarding getting rid of the Months Table.

    The reason I went down that route was to get the Record/Table structure below, I was thinking of having a Sub Datasheet within a main form, linking The EmpID Main Form to EmpID in the Holiday table Datasheet

    The staff chose dates/Days, 1,2,3,4 = 4 days or it could be 11,12,13,17,18,19 6 days with their days off included in the middle that's why I wanted Months in the table and days used.

    The employee will require a print off of his booked holiday and previous booked holidays afterwards.

    If i enter in 11.12.2017,12.12.2017,13.12.2017,14.12.2017 in the dates section, what would I need in a month field or query calculation to display to the user a format similar to the below.
    Hope that makes a little more sense, I'm basically moving everything from Excel into a Database. (See Excel Version Below)

    Attachment 27789

    Attachment 27790

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

Similar Threads

  1. Replies: 3
    Last Post: 07-02-2015, 09:15 AM
  2. Replies: 4
    Last Post: 11-04-2013, 02:24 PM
  3. Table and Relationship Design
    By GrayWolf in forum Access
    Replies: 6
    Last Post: 01-31-2012, 02:04 PM
  4. Schedule: Table/relationship structure
    By capnponcho in forum Access
    Replies: 1
    Last Post: 12-18-2011, 01:24 AM
  5. Complex Survey: Table Structure Design and Normalization
    By kevin007 in forum Database Design
    Replies: 2
    Last Post: 07-06-2010, 09:21 AM

Posting Permissions

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