hi guys,

Really sorry to ask this, its been a while since ive used access.

I have a very simple idea and i know its a simple answer, but i just cant remember what to do!

I have 3 tables - members, arrangements, venues!

I only want one member to select the same venue once, if you get me e.g. Mr Doe - shopping, if that was done once I wouldnt want to be able to put it in twice.

I have primary keys for them all and 2 foreign keys in the arrangements table. I just cant figure out what I have to do to prevent this!?

Please help!

Dan

How about a table with Member and Venue columns with both columns as primary key.

Combined entries must be unique

Len B

hey,

I have an arrangements table where its supposed to take all the arranged people and set them to a venue.

I have Arrangements_ID as the primary key and then Member_ID and Venue_ID as foreign keys. And yet I can still put for example John Doe on the same venue twice! I think its a problem with relationships, but i dont know wot to do.


Can you help??

Thanks Dan

Alternatively you could set up an index. This would mean you not having to tweak your relationships

Create a Unique Index Member ID and Venue_ID.

What purpose does the Arrangements_ID pk serve. Could you not use Menber_ID and Venue_ID as pk and they can still serve as fk's

Any way would seem to give you a solution

len b

i take it you mean deleting arrangments ID.

I still want say one person to be placed on many different venues and one venue to have many different people on it. Just not the same person going to the same venue twice.

Would this work if i JUST had primary keys of members and venues combined in the same field?

Isnt it better normalised is i use another primary key like the arrangements one and that way it would work with some reconfigurement of the relationships?

Dan

I prefer to use "natural" keys for ables when they are available rather than surrogate keys. ArrangementID in this context is a surrogate key. To make a multi-field primary key (you can have up to 10 fields in a key or index), click on the first field and then use Cntl-click to select each additional field. When all the key fields are highlighted, press the key icon on the toolbar.

If you want to use ArrangementID as your pk because you need to make a child relationship and you prefer to use one key field rather than two, then that's fine. You just need to define a unique index on the other two fields. To do that, open the indexes dialog, type a name for the index, choose the first field, then choose the second field on the next line being sure to leave the index name field empty. Make sure to define the index as unique. You also need to define each field as required.

Hi Dan Had a problem myself yesterday as well.

No doubt you will note the comment from Pat re keys but in reply to your pm:

Having the two key primary key would allow

A A
A B
A C
B A
B B
B C

but would not allow any of those combinations to be duplicated.

Pat's comment about primary keys are very relevant. From your pm and the point re normalisation. Does Arrangement_ID actually convey any information about the relation?. Do you need it at this time for a child relationship ?.

My feelings would be that if it is not needed then remove it. It is somethingless to manage.

Len B

I know there are many opinions on pk's.