Concatenated data as primary key? Advise please.

[6ftAndClean]

I have a 3 tables (Wards, Areas & Streets) which have the following relationship:

"Wards" "Areas" "Streets"
Ward ID ------ Ward ID Ward ID
Ward Area ID ------ Area ID
other fields Key Street Identifier
other fields

The problem is that I am trying to avoid the field 'Area ID' in the table 'Areas' being an AutoNumber primary key by which I link the group of streets occuring in that area (thus the inclusion of a seperate field 'Key'to be a primary key).

The reason for this is that there are around 20 Ward records and around 25 Area records per Ward. I would like to be able to utilise the Area ID to refer to as I input data from my mapping software. In other words for each Ward the Areas within it should be numbered 1 upwards. How can I do this and still be able to correctly select the right streets for an area? I had wondered if using some sort of concatenation of 'Ward ID' and 'Area ID' would help to form a workable primary key but I can't see how to implement this.

e.g.

"Wards" "Areas" "Streets"
Ward ID ------ Ward ID
Ward Area ID
other fields Concatenated Key -- Concatenated Key
Key Street Identifier
other fields


If 'Ward ID' = 01 and 'Area ID' = 15 'Concatenated Key' would be 0115 (Access would shorten this to 115 but that should not present any particular problems). I can easily append the concatenated number to the data as I import it to Access.

Thanks for your help.

 

[BNPMike]

6ft

Somehow I can't quite see your problem. Firstly you don't need autonumbers. Secondly you don't need 'Key'in "Areas". The key will either be Area ID or Ward ID + Area ID if you are re-using Area IDs as you want to.

Maybe the answer is simply you can declare Ward ID and Area ID as the primary concatenated key. You realise you can do that - just select the two fields in the Design Screen and click the key symbol.

 

[6ftAndClean]

My bad. Forget 'Key' in 'Area ID'. It was not necessary in the 1st instance and a typo in the second (should not have been there).

Thanks for the tip, I now see that I can select 2 fields as Primary Key. I seem to have a problem doing this however as Access will not let me have duplicated values in a primary key field. Go to this URL to see example tables of the data I am using and the relationship window layout(currently missing some links)-

http://homepage.ntlworld.com/e.clare/access_forum/

You will see that the fields I wish to use as keys contain many duplicated values. How can I work with this data without having to use a separate key?

I'm sorry if this seems like the most basic/obvious thing in the world to you but it's all new to me.

 

[BNPMike]

Your model looks alright to me. In addition to the links shown, you have a one:many between area and streets but you're ok without that being explicit.

A key is something which doesn't have duplicates. Your sample data doesn't however show any duplicates. For a concatenated key Access shouldn't be rejecting anything unless the values for both fields are the same as an an existing record.

Can you give an example of data that is being rejected?

 

[BNPMike]

Sorry I've just noticed you have a duplicate in the Ward data - two shown as 5/1. You're just going to have to change one of them to 5/6 or whatever. Do the same for any others before you put the primary key on.