Results 1 to 7 of 7
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    relationships, referential integrity, can't enforce

    I have never been good at relationships (sounds like a bad first date opening line)

    two (2) tables:

    tblManufacturers
    tblManufacturers_RepAgencies

    both have a field: RepAgency (both: short text, 255 char )

    a RepAgency can have many Manufacturers
    a 'Manufacturer' can only have (1) RepAgency -OR- may not have a RepAgency

    trying to enforce referential integrity between the 2 tables so that if a RepAgency changes its name (they usually do not, but once in a while ...they do), or dissolves (that happens all of the time), the change cascades into tblManufactcurers

    creating a queries:


    Find duplicates for tblManufacturerRepAgencies yields none
    Manufacturers Without Matching tblManufacturerRepAgencies yields only Manufacturers with no RepAgency (which is a valid entry)

    in tblManufacturers_RepAgencies, the field: RepAgency is a required index,
    consequently, there can be no records with a null RepAgency (to match Manufacturers with no agency)

    and a possible work-around (if one is needed to address this condition)
    ... RepAgency: "-none-"
    really isn't something we want to do (although ...reluctantly... we could)


    any thoughts will be appreciated with thanks in advance
    m.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Mark,

    It would be helpful and efficient if you could attach a copy of your database so readers can see and "experiment" with the issue using sample data.
    I recommend the tutorials from RogersAccessLibrary in this Database Planning and Design link for learning about tables and relationships. If you work through a tutorial or 2, then you will experience a process that can be used with any database. Each tutorial will take about ~30-45 minutes.

    Unmatched and Duplicate queries are part of the MS Access wizards.

    Good luck.

  3. #3
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496
    Db tables attached for play
    Attached Files Attached Files

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I haven't got time to look at your database right now but see this article for a bit of relationships advice: http://www.mendipdatasystems.co.uk/r...ps1/4594533224.
    It also covers referential integrity and cascades
    Last edited by isladogs; 10-26-2019 at 01:58 AM. Reason: Spelling
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    377
    Hmm, well let's not use *Rep Agency* as the PK. Use Autonumber (yep, add a separate field) and then use the FK in tblManufacturers. Then it it would not matter if they change names because it would simply pull in the new name from the FK.

    Also, remove that Look-Up for Rep Agency. You should be using Forms to view data and you can put a Combo Box there. You never want your Users in the tables. I would also add an Autonumber PK to tblManufacturers in the off chance you are going to use that Relationship further down the road.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with the advice/comments from Colin and Gina. I would also recommend you tell us a little about th business this database is intended to support. I see some potential Entities hidden in tblManufacturers.

    Possible hidden but important entities

    Contact (especially if there can be more than 1 and if the contact(s) are responsible for different areas/things/categories.)
    Parent??
    GeoRegion (North America,Asia Pacific, European??)
    Grade??

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by markjkubicki View Post
    two (2) tables:

    tblManufacturers
    tblManufacturers_RepAgencies

    both have a field: RepAgency (both: short text, 255 char )

    a RepAgency can have many Manufacturers
    a 'Manufacturer' can only have (1) RepAgency -OR- may not have a RepAgency
    OK, so far, so good.

    Table "RepAgency" is the one side and table "Manufacturers" is the many side of a "1-to-many" relationship.
    But then you say
    Quote Originally Posted by markjkubicki View Post
    trying to enforce referential integrity between the 2 tables
    However you previously said
    Quote Originally Posted by markjkubicki View Post
    a 'Manufacturer' can only have (1) RepAgency -OR- may not have a RepAgency
    EVERY record in table "Manufacturers" MUST be related to a record in table "RepAgency" to set referential integrity (RI)! You cannot have RI if this is not true.
    If there is a record in table "Manufacturers" that is NOT related to table "RepAgency", then you have an orphan record. And this is not allowed if RI is set.


    Having a record in table "RepAgency" of
    RepAgency: "-none-"
    might be the way forward. Or you will have to figure out another design. Since I know nothing about your project, I can't make any recommendations.


    With regards to the PK field: you should read Microsoft Access Tables: Primary Key Tips and Techniques
    Virtually every table in my dBs have an Autonumber type field as the PK field.


    AutoNumber
    ---------------.
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.


    Look-up FIELDS: see The Evils of Lookup Fields in Tables



    Good luck with your project...

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

Similar Threads

  1. Enforce Referential Integrity Limit
    By Western_Neil in forum Database Design
    Replies: 8
    Last Post: 05-12-2018, 09:14 AM
  2. Unable to Enforce Referential Integrity
    By DaveT99 in forum Database Design
    Replies: 3
    Last Post: 04-17-2018, 02:34 PM
  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: 2
    Last Post: 04-18-2013, 05:56 AM
  5. Referential Integrity
    By U810190 in forum Access
    Replies: 1
    Last Post: 03-29-2010, 05: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