Access table setup to avoid duplicates

PrzemoF

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?

Esel

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

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.

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).

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