Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Access table setup to avoid duplicates

  • 05-05-2016 09:20AM
    #1
    PrzemoF
    Registered Users, Registered Users 2 Posts: 1,929 ✭✭✭


    It seems to be trivial, but somehow I'm struggling with this problem. I'm using access with lookup.

    Tables:
    PRODUCER
    ID (unique, primary key)
    PRODUCER_NAME

    PRODUCT
    ID (unique, primary key)
    PRODUCT_NAME
    PRODUCER_NAME (linked as lookup to PRODUCER table)

    I want to avoid having duplicates in the PRODUCT table. So there might be multiple positions with PRODUCT_NAME = 'Pasta', but they cannot have the same PRODUCER_NAME.
    This is OK:
    ID=1, PRODUCT_NAME = 'Pasta', PRODUCER_NAME='Roma'
    ID=2, PRODUCT_NAME = 'Pasta', PRODUCER_NAME='Irish Pasta Industry'
    but this is not:
    ID=1, PRODUCT_NAME = 'Pasta', PRODUCER_NAME='Roma'
    ID=2, PRODUCT_NAME = 'Pasta', PRODUCER_NAME='Roma'

    Obviously I can't force PRODUCT_NAME to be unique, so I need some method of checking if an entry with a certain combination of PRODUCT_NAME and PRODUCER_NAME is already in the table. I'm OK with VBA, but I want to keep things simple, so I'd prefer to do it without VBA.

    How to solve it?


Comments

  • #2
    Esel
    Registered Users, Registered Users 2 Posts: 24,331 ✭✭✭✭Esel
    Not Your Ornery Onager


    Just an idea - concatenate the two fields into a third field, and make this unique?

    Not your ornery onager



  • #3
    drag0n79
    Registered Users, Registered Users 2 Posts: 197 ✭✭drag0n79


    Your PRODUCT table would be better with PRODUCER_ID rather than PRODUCER_NAME, and then make your primary key on the PRODUCT table (ID, PRODUCER_ID), i.e. it's a composite primary key with those 2 columns.


  • #4
    PrzemoF
    Registered Users, Registered Users 2 Posts: 1,929 ✭✭✭PrzemoF


    PRODUCT table in field PRODUCER_NAME has link to PRODUCER.ID, but it's access lookup, so I named it PRODUCER_NAME.
    Anyway, thank for the suggestion: composite primary key sounds exactly like something that I need!

    I'll try both (a calculated unique field MODEL_NAME + PRODUCER_NAME).


  • #5
    PrzemoF
    Registered Users, Registered Users 2 Posts: 1,929 ✭✭✭PrzemoF


    I made primary composite key from PRODUCER_ID (lookup) and PRODUCT_NAME and I kept ID as autonumber field, but it's not primary key anymore. That seems to work.

    I calculated unique field is not an option in ms access


Advertisement