More than one unique field - simple one. (1 Viewer)

[JakeThePeg]

Wotcha, folks.

I'm in a right pickle at work at the moment.

For the life of me I can't make an Access table have more than 1 unique field.

For example, I want a company's name and postcode to be treated as unique ONLY when they appear together.

So I can have many companies with the same name and different postcodes, but no doubles of the same company name and same postcode.

Does this make sense? How do I trigger a unique property when two certain fields match a previous entry.

I've searched on the net and in these forums, but i can't find help. Maybe it's the way I'm wording it.

So, sorry if this is incredibly straight forward, and I'm being a noob, but this is rather urgent.

Many thanks.

Jake.

 

[Len Boorman]

Either both fields as primary key or a Unique index again against both fields

L

 

[Canderel]

Concatednated primary key.

You just need to set your postcode AND company name together as the primary key.

On design view, click the two rows (either by clicking and dragging (if they are next to each other), or by holding down Ctrl while clicking the second one), and then make them the "concatenated primary key".

Then if one tries to enter a duplicate, it will not allow it. It should not be case sensitive...

Ie.
MyCo
9912

=

myco
9912

-Reenen

 

[JakeThePeg]

Okay, I'm a bit more confused now.

I have a very simple address database going on.

I have an auto increment ID number as the primary key(ed) field.

But I don't want a combination of COMPANY and POSTCODE to be the same.

Eg. I only want a single McDonalds in SE1 1YZ. Another McDonalds in SE1 1YX would be fine.

When I try to do this concatenated primary key thing, it removes the primary key from my ID nos.

Should I state that I'm using the Access DB for a Visual Basic project.

Please help :-(

J.

 

[neileg]

No, leave your autonumber as the primary key. You need to set your co name and post code as a compound index set to no duplicates. Click the indexes button on the toolbar in table design view and add a new two field index.