Results 1 to 2 of 2
  1. #1
    braddaz is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Posts
    1

    Unhappy "Enforce Referential Integrity" doesn't recognise correct relationships

    Hi all,

    I'm a student doing a project for coursework, but I'm also using this project for the company I work for. I am using MS Access to make a database to store employee details, the shifts, and the day they work. The outline for the database looks like this:

    Lifeguard(lgID, fname, sname)
    Shifts(ShiftID, startTime, endTime, shiftFrequency, weekStart)
    DayWorked(lgID, ShiftID, Day)

    I don't really know how to structure the DayWorked table to get the desired outcome. My problem is, Lifeguard should have a many-to-one relationship with DayWorked. Same with Shifts. However, when I click 'enforce referential integrity' on Access, it claims that it is one-to-many which is the opposite of what I need. I have tried messing about with the primary keys of DayWorked but nothing has solved my problem. I'm at my wits end and I don't know what's wrong with it.

    An example of the relationship would be "a lifeguard cannot work multiple shifts on the same day, but can work multiple days and multiple shifts". Same concept with Shifts.



    I'm really not sure what to do from here, I've read so many other posts but still don't understand. Any help would be massively appreciated. Thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    DayWorked is a junction table to relate Lifeguards with Shifts - this is a many-to-many relationship. Lifeguards and Shifts are not directly connected. Lifeguard and Shifts each link to DayWorked in Relationships builder.

    Set lgID and shiftID and Day as compound index in DayWorked to prevent duplicate combinations.

    Is Day a date/time field? Day is a reserved word and should not use reserved words as names for anything.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. relationships, referential integrity, can't enforce
    By markjkubicki in forum Programming
    Replies: 6
    Last Post: 10-25-2019, 04:40 PM
  2. Enforce Referential Integrity Limit
    By Western_Neil in forum Database Design
    Replies: 8
    Last Post: 05-12-2018, 09:14 AM
  3. Is it always bad to NOT enforce referential integrity?
    By Access_Novice in forum Database Design
    Replies: 8
    Last Post: 08-18-2014, 09:59 PM
  4. Replies: 6
    Last Post: 05-14-2013, 09:16 PM
  5. Replies: 2
    Last Post: 04-18-2013, 05:56 AM

Tags for this Thread

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