Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14

    How do you prevent duplicate records?

    Hello. I know how to prevent duplicate fields by going to Index in design view and setting it to no duplicates, but how do I set records to not be duplicates?

    For example, I have 3 tables

    Table 1 - a list of movies. Fields - AutoNum, Movie Name
    Table 2 - a list of actors. Fields - Autonum, Actor name

    Table 3 Relationship table showing which movies have which actors. Fields - Autonum, movie, actor

    Now, an actor can be in multiple movies, and a movie can have multiple actors.

    But when I'm entering the data into the relationship table I don't want to enter the same movie - actor combination twice, but each record still has a unique autonum.

    How do I do this?

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    by having what is called a join table


    tblJoins
    JoinPK autonumber
    MovieFK long - links to movie table
    ActorFK long - links to actor table

    You can set the two FK fields as a combined index - no duplicates

  3. #3
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Thanks for that info, but it didn't really help me as I don't know how to do what you're saying...

    I don't know how to make a join table, but what I did was click on "Index" in the ribbon and create what looks like a table of indexes, and added the two fields to the new index I was making. That's the combined index you're talking about right?

    Is this different from a join table?

    Or is that just another name for a relationship table...Anyway. Composite relationship was what I needed. Thanks.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you use a join table to map many to many relationships - one movie, many actors, one actor many movies. It's just a table, you make it the same way you make any table. And rereading your original post, your table 3 is a join table.

    the combined index (if set to no duplicates) is used to ensure you can't map the same actor to the same movie (or visa versa) more than once.

  5. #5
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Hi. Thanks again for answering that question. Do you mind if I add another one without creating another thread?

    Now I have 3 tables, Table A, B and C.

    Table A and B are joined, with A as the Primary, and B as the Foreign.

    However, I needed to create a Table C because its data is different from Table A, but I need to join it to Table B, in the same field where the A-B join is.

    So I already have an A-B join. But I want to join C-B with the same field.

    So the resulting field in Table B will only be allowed to have data from BOTH Table A and Table C, but nothing more (i.e. there's referential integrity).

    If I could put the Data in Table C into Table A, then it wouldn't matter (and the A-B relationship would suffice), but I really need it to be in it's own table.

    No matter which option I select in the "create relationship" header, I get the "referential integrity cannot be enforced" message.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You can only enforce referential integrity if the fields in tables A and C are primary keys - so no reason why you shouldn't be able to it. BUT - before you can create a record in table B, the PK records in both tables A and C will already need to exist - and if the PK's are autonumbers there is a risk they will go out of line. If your tables already contain data, table B must not have any FK values which are not in both tables A and C.

  7. #7
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Access is letting me enforce referential integrity on my non primary key fields though. In all of my tables, I have an auto-number that acts as a primary key, but I use the field that actually holds the useful data for the join tables, because I think it's a pain to have to sort through all these seemingly random numbers. So my join tables don't have any primary key as a foreign key (they have their own primary key autnumber though).

    I can understand that if my PK's are auto-numbers it means I would immediately have duplicates.

    Table B was already filled with a bunch of data from Table A, so when I try to enforce referential integrity from Table C to Table B it says that I can't because "it has data that isn't found in Table C" The default example Access gives me is that I might have an employee in B that isn't found in C since C is where B gets its reference from. So in my One-to-Many relationship, there are entries in the "Many" that correspond to A's Data, but not C's and that is what seems to be the problem. I can't seem to find the button or protocol or whatever that allows me to make B draw from C's data.

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what referential integrity means is a child record cannot be created unless the parent already exists - and from what you are saying, the parent does not exist in table C, so the integrity cannot be created
    allows me to make B draw from C's data
    you don't need referential integrity to do that

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    further edit - add the missing records into table c and you will then be able to create referential integrity

  10. #10
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    Right, but I don't want any child records in B that don't appear in the two parent records A and C, Which is why I want referential integrity.

    The "missing" records in C are the records that originate from A and I want to keep those two data sets separate.

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    you seem to be describing an 'either/or' reverential requirement which is not possible. Why not use real names rather than A/B/C, and describe exactly what you are trying to do and why

  12. #12
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14


    The two parent tables are Character and Component, and the relationship table is R Character Breakdown

    I am breaking down Chinese characters based on their etymologies and form to create searchable referencing database for finding the similarities between and meanings of the ancient forms.

    I update Component and Breakdown as a new character gets added in the Character Table, which is why "Component" is scant at the moment, because I haven't needed it until now.

    The problem with Character components is that it actually creates problems for Access.

    You can try this yourself: Make a table with an indexed (no duplicates) field and then try to put in these two records: and Access thinks these two different characters are the same, even when they have different unicode numbers. I bounced this issue on the official microsoft access forums (if they were "official") and everyone there experienced the same problem. I tried to do this back when I used Access 2013, but now that I moved up to 2016 Access doesn't even recognize ⺮ and puts it as a ? in a box.

    The buddies over at the other forum told me it is a collation issue that they had no direct fix for. This is one of the reasons why I make "Component" a separate Table. Character components aren't real characters, just parts, and also because the collation error prevents me from indexing it without duplicates, but the character component table has a very limited amount of entries as opposed to the 40,000 characters in the Character Table.

    In the character breakdown, On the left I have the Character I am breaking down, and on the right I have the pieces that make it up. Sometimes the pieces are full characters, other times they are the "components."

    So that's why I want the child field to have two parents.

    Here's the thread from the microsoft answers: http://answers.microsoft.com/en-us/m...0-4f07be977ecf

    Edit: I grayed out that text because I put in the wrong ⺮ for the new test I was doing because I changed my Chinese keyboard since then. Just ignore the grayed out text. I didn't see any strike out formatting.
    Last edited by cwjakesteel; 11-06-2016 at 05:52 PM. Reason: Added the correct characters to use for the duplicate test.

  13. #13
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    I just looked back on the old version of my database from Access 2013 and the database seems to have automatically deleted the records containing and 㔾 . So I wanted to put the "problem" characters in its own table so that they don't interfere with the others because there's really no reason to.

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Interesting project!

    However, it doesn't alter the fact that to maintain referential integrity, you cannot have a child record without a parent record in that relationship. Even if you linked on the ID fields, it would not change the requirement.

    Perhaps you can approach this from another angle - assuming this is your issue

    Access thinks these two different characters are the same, even when they have different unicode numbers.
    store the Unicode numbers in a separate field and make that your primary and family key - it will need a small amount of code on a form before update event - something like

    me.CharacterID=ascW(me.character)

    and can easily be viewed in a query or form as a column chinchar:chrw(characterID)

    It does sound like you are trying to use tables as a means of data entry and manipulation - something they are not designed to do, you will have much more flexibility in a form and won't need to see those pesky numbers

  15. #15
    cwjakesteel is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2016
    Posts
    14
    You're right I'll have a much easier time with forms, but I wanted to give my tables some bulk before made forms that represented everything that I want to enter.

    Also, the problem isn't the collation error anymore. Unlike "Characters", "components" will be a very small set of data so I don't have to worry so much about entering the same data twice. So I can leave it as Indexed (with duplicates).

    So what you're telling me is that a Parent -> Child <- Parent relationship cannot work at all? So I can't give a child field two parent fields as long as the parent fields have completely unique data? In that case, I might as well make another field in the "R Character Breakdown" that will act as a sister field solely for accepting data from the "Component" field, while the current child field will still take data only from the "Character" field.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 08-05-2014, 07:06 AM
  2. Replies: 4
    Last Post: 12-18-2013, 06:52 PM
  3. How to prevent duplicate records
    By talktime in forum Programming
    Replies: 7
    Last Post: 05-15-2013, 11:02 PM
  4. Prevent duplicate records for calculated field
    By LoveLEE143 in forum Access
    Replies: 3
    Last Post: 05-03-2013, 11:46 AM
  5. Replies: 2
    Last Post: 02-12-2011, 09:54 PM

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