Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Oct 2001
    Location
    Indiana, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No duplicates (Access 2000)

    I am creating a database that tracks requested changes to parts, I have a many-to-many relationship between the two. I am now creating a form that will allow review and approval of the requested changes, my problem is that I get duplicate records of any request with multiple part numbers.
    Can anyone offer any words of wisdom or moral support?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No duplicates (Access 2000)

    Mark,

    You have to create a query with a one to many relationship.
    If you want the requests with there parts put the request on the one side.
    If you want the parts with the request they are on , put the part on the one side.
    You can change the relationship in the design view of the query, independently from the relationship window. Just double click on the line between the tables.

    Hope this help
    Francois

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: No duplicates (Access 2000)

    I'm assuming 1 Requst can be for multiple part? In that case, you need 3 tables. A Parts table (pk = PartNo); a Requests table (pk = ReqestNo), and a RequstPart table (pk = RequestNo, PartNo). You would have a one-to-many relationship between Parts and RequestParts, based on the PartNo.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Oct 2001
    Location
    Indiana, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No duplicates (Access 2000)

    Thank you Francois, I have a query already that I am using for my form. The query includes the Request table and the "junction" table I am using to create my many-to-many relationship. The Request side is the "one" and the "junction" side is "many". Should I set it up this way, or should I get my PartNumber from the PartNumber table?

  5. #5
    New Lounger
    Join Date
    Oct 2001
    Location
    Indiana, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No duplicates (Access 2000)

    Thanks Mark, I am set up as you describe, but I continue to get dulplicate displays of the request record on my form when I have multiple part numbers?

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No duplicates (Access 2000)

    Do you use a form with a subform ?
    You should use a form based on the request, with a subform based on the junction table (or a query with the junction table linked to the part table) and the link field should be the request ID
    I have made a sample see attachment
    Attached Files Attached Files
    Francois

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: No duplicates (Access 2000)

    I would guess your problem is in the identification of the Master and Child Linking fields between the mainform and the subform. Check the properties for the subform control.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    New Lounger
    Join Date
    Oct 2001
    Location
    Indiana, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No duplicates (Access 2000)

    You're right. After another attempt it works great!
    Thank you, for your help
    Mark

  9. #9
    New Lounger
    Join Date
    Oct 2001
    Location
    Indiana, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No duplicates (Access 2000)

    Thank you for your help! It's working great now.
    Mark

  10. #10
    New Lounger
    Join Date
    Jan 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No duplicates (Access 2000)

    What if you assigned each user a different group of numbers to use as their primary key numbers? UserA could use numbers 1-999, UserB 1000-1999, UserC 2000-2999, etc., to eliminate duplicates?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •