Results 1 to 11 of 11
  1. #1
    WBosman is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    Vancouver
    Posts
    6

    Avoiding Duplicates - Concatenate Related?

    I'm fairly inexperienced with MS Access; been using it for about a week now. The last time I did any programming was about 4 years ago, so understanding the vba coding has been a bit of a struggle for me.



    I'm making a database for about 200 courses that will be used to organize 3 years of training for a program. I've produced a 'many to many' relationship for a table of "courses" and a table of "contacts", whereby one course can have many contacts and one contact can be involved with many courses. My issue is that when producing the query and thereby the form, I receive one course for each contact resulting in multiple courses being displayed.

    My goal is to have the form have a spot for the contact (and their phone number) to be filled into, then give the option to fill in a new contact. This list of contacts I guess would be stored with the specific course. As well, an autocomplete to avoid possible duplicate contacts with mispellings.

    I have asked many of my comp sci friends and they didn't know access to well. Even the one doing her masters in references to databasing seemed kinda stumped. I feel like this shouldn't be as difficult as it has proven to be. One suggestion has been to concatenate the data, but I've been having issues with the modules. I will attach my database in hopes that it clarifies the problem.

    I appreciate any help, and again please go easy on me, I'm not a pro with VB coding or MS Access, thanks.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have a data model? There are free models at
    http://www.databaseanswers.org/data_...ters/index.htm others may be more appropriate

    To prevent duplicates with M:M, use Junction table.
    one site http://support.microsoft.com/kb/304466

    In the junction table, make a unique compound key from the id of each of the tables that feed the junction table,

    eg, Junction Table
    ID PK
    TblOne_Id
    TblTwo_Id

    Unique Index on TbleOneId + TblTwoId

    This will prevent duplicates in the junction table. Access will highlight a message (I think 3022 but that's a guess)


    Note: I don't have Acc2007 so can not open your accdb. Many readers do not have the ability to open accdb.
    You may want to save/post a copy of your database as mdb. You may get more responses.

  3. #3
    WBosman is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    Vancouver
    Posts
    6
    My model is very similar to http://www.databaseanswers.org/data_...ling/index.htm
    which appears to include the many to many relationship with instructors and classes.

    I'm actually already using a junction table to get the many to many relationship, but I believe it's the junction table that's creating my duplicate form issue. I'm not sure about ridding the junction table of duplicates because since I need to have duplicate CourseIDs with unique ContactIDs.

    Attached is zip of a .mdb copy of my work, although my macro won't work, but it's not important.

    Thanks for helping orange, I appreciate it.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There is some confusion here.
    The data model does NOT have a Many to Many between Classes and Instructors. The modeler has resolved this but means of the junction table,
    Instructors_Classes. This table shows/records which Instructors teach which Classes. There are no duplicates in this table. The table records the combination of Instructor_Id and Class_Id.


    Code:
    I'm actually already using a junction table to get the many to many  relationship, but I believe it's the junction table that's creating my  duplicate form issue.
    No, the junction table does NOT allow you to get the many to many. The junction table is the technique to avoid/remove/reconcile the many to many by making two 1:M relations.
    Code:
    I'm not sure about ridding the junction table of duplicates because  since I need to have duplicate CourseIDs with unique ContactIDs.
    This does not make sense to me. Perhaps it's your wording, but perhaps you could define Contact, so things are more clear.
    Last edited by orange; 05-17-2011 at 12:56 PM.

  5. #5
    WBosman is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2011
    Location
    Vancouver
    Posts
    6
    I think my wording was bad. I meant to imply that the junction table produces the M:M-like relationship. Unless my understand of M:M relationships is completely flawed, which is completely possible.
    Also I realize my wording of duplicates may have been misleading. The combination is unique, but it is this unique combination that causes me to produce multiple form pages for the same course, with different contacts.

    Maybe I can explain it this way:

    CourseContact (Junction Table):

    ID CourseID ContactID

    1 1 1
    2 1 2
    3 2 3
    4 3 1
    5 3 3

    Whereby there are 3 courses and 3 contacts (such as instructors) and 2 'instructors' may teach one course, etc.

    My form produces 2 pages for CourseID 1 (and CourseID 2) where each page will have the same properties except the different contacts. My goal is to have a single page for each CourseID with the two unique contacts on that one page.

    Hopefully that clarifies it a bit more, sorry.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I looked at your database. In the CourseContact table, you have a PK made up of each of the fields. I would leave the CourseContactId as PK, but the other 2 fields should be removed from Primary, and should be parts of a unique index ( Primary no, Unique yes, Ignore null no).

  7. #7
    WBosman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Vancouver
    Posts
    6
    CourseContactID is not actually referenced anywhere besides in the Course Contact table, so personally I don't see the importance of even having it except to avoid errors in case of null inputs (I think)

    If the CourseID and ContactID entries are unique, I will get the single form I want, but would be unable to designate multiple unique contacts to courses or multiple unique courses to multiple contacts given either ContactID or CourseID is unique.

    I think I will just make this easier and have 3 possible Contact fields and assume that that is the limit. Not ideal but still works in a crude way I suppose.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    CourseContactID is not actually referenced anywhere besides in the Course Contact table, so personally I don't see the importance of even having it except to avoid errors in case of null inputs (I think)
    If CourseContactId is PK and autonumber, attempting to leave a Null value will force error message.

    If the CourseID and ContactID entries are unique, I will get the single form I want, but would be unable to designate multiple unique contacts to courses or multiple unique courses to multiple contacts given either ContactID or CourseID is unique.
    Wrong, but I think it's because of the wording. It isn't that CourseId and ContactId are unique. It is that the combination of CourseID and ContactId is unique. By being unique, Access will not allow any duplicate/replicate values.

    Consider:
    Courses
    CourseId Coursename
    A1 Biology
    S3 Nutriceuticals
    C5 Advanced Philosophy

    Contacts
    ContactId ContactName
    1 John Smith
    2 Billy Bob
    3 Jane Doe
    4 Black Catt

    CourseContacts

    CourseId ContactId
    A1 1 (Biology John Smith)
    A1 4 (Biology Black Catt)
    C5 1 (Advanced Philosophy John Smith)
    S3 3 (Nutriceuticals Jane Doe)
    Multiple Contacts to Course Biology

    Multiple Courses for a Contact (John Smith)

    Maybe I'm missing something, but I feel the unique composite index is the essence of the junction table.

  9. #9
    WBosman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Vancouver
    Posts
    6
    Okay yea, it's just been the wording. I thought you meant the individual fields needed to be unique. Of course the combination will be unique, but this is where my problem lies actually. Instead of having a form appear for each unique combination that is formed, I want a single form for a course where there are multiple contacts on that form.

    So from the tables you mentioned, I'd want to be able to get a query/form with the result:

    A1 1(John Smith) & 4(Black Catt)
    (one course and 2 contacts)

    where I am currently receiving:

    A1 1(John Smith)
    A1 4(Black Catt)
    (2 results for the same course with unique contacts)

    I'm not sure if this is possible with MS Access.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could have a setup such as a Main form with Course and a subform with contact.
    On your mainform you'd see A1 Biology
    and there would be 2 entries in the subform
    .John Smith
    .Black Catt

    This is a very common setup when dealing with 1:M relationships.

    Person --- Hobbies
    Order ---- Order Details
    Team ---- Players
    Loan -----Payments
    etc.

    I suggest a little googling looking for some examples.
    Here's a couple of links (I just did the google)
    http://www.techonthenet.com/access/subforms/
    http://fisher.osu.edu/~muhanna_1/837...s/subforms.pdf

    Good luck.

  11. #11
    WBosman is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Vancouver
    Posts
    6
    Cool, I'll give it a try.

    Thanks for the help. =)

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

Similar Threads

  1. Replies: 1
    Last Post: 01-08-2011, 06:34 AM
  2. Avoiding a cartesian product
    By johnmerlino in forum Queries
    Replies: 0
    Last Post: 10-25-2010, 07:52 AM
  3. Multiple joins, avoiding cross joined result
    By richjhart in forum Queries
    Replies: 2
    Last Post: 09-17-2010, 09:32 AM
  4. Avoiding duplicates in query
    By theracer06 in forum Queries
    Replies: 1
    Last Post: 08-24-2010, 12:49 PM
  5. Replies: 1
    Last Post: 08-18-2010, 02:05 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