How to create unique primary key?

[jinalpatel]

I want to create a primary key that is combined with several fields

Autonumber+InspectorNo(1 to 5)+CountyNo(1 to 35)+Date

How to create it ? Where to write code for it?
Please help!!

[DThreadgill]

Add another field to your table (such as primarykey and set the datatype to text).

using an update query, update that field:
[ID] & [InspectorNo] & [CountyNo] & [Date]

or if Date is not a field name in the table
[ID] & [InspectorNo] & [CountyNo] & Date()

The ID field in this instance is AutoNumber and is the primary key.

[ADezii]

@jinalpatel

1. Open the Table containing the above Fields in Design View.
2. Select the [Autonumber] Field.
3. Hold the CTRL Key Down, then select the [InspectorNo], [CountyNo], and [Date] Fields.
4. All 4 Fields should now be selected.
5. On the Menu Bar, Click Edit ==> Primary Key.
6. You have now just created a Composite, Primary Key which means that the combination of ALL 4 Fields must be Unique within the Table.
7. The following Records would not be a Primary Key violation, since the combination of these Fields is Unique:
   

   Autonumber]    [InspectorNo]    [CountyNo]    [Date]
       1                4              51        6/26/2009
       2                3              51        6/26/2009
       3                2              37        6/26/2009
       4                5              37        6/26/2009
    

P.S. - You mentioned writing code in order to create this Key. This would be much more difficult, and I'm not exactly sure why you would need to do this programmatically.

[OldBirdman]

Why is such a primary key is needed? Although I understand the answer of how to do this, I don't understand the reason. Any dependent tables would not remain related if a value were changed, perhaps because of an entry error.

With a field of type AutoNum, there is a unique key. ADezii's concern

6. You have now just created a Composite, Primary Key which means that the combination of ALL 4 Fields must be Unique within the Table.
7. The following Records would not be a Primary Key violation, since the combination of these Fields is Unique:

is no concern as a non-unique key cannot be generated here.

Is this better addressed in a discussion about Indexes vs. keys?

[mshmyob]

I think the problem lies in what the OP is really asking for.

Does he want to create a new table with a primary key value consisting of all the field values he mentioned - answered in 2nd post by DT how to do that.

or

Does the OP want to create a composite primary key with the existing table. - this was answered by Adezii post.

OB's concern about existing table relations is valid if the OP now wishes to change the PK to a composite.

Just as a side note if the OP actually wants a composite PK then the AUTONUMBER field would not be needed to be included if 'Each inspector cannot inspect the same county on the same day'

Just my 2 cents for this discussion.

cheers,

[ADezii]

@mshmyob
Makes perfect sense to me, mshmyob. Let's wait and see what the OP is really looking for.

[jinalpatel]

OK I am sorry for replying late but I was on vacation. I need this kind of primary key because I am working with pendragon software which syncs with PDA units. It is possible that if two inspectors are inspecting the same nursery at the same time(thats why I am taking "seconds" in consideration), if the nursery farm is more than 300 acres.
I need to create this unique primary key to keep the master records unique.
Thanks everyone for answering and meaning ful discussions!! Thanks much!!

[jinalpatel]

also, pendragon software creates its own tables. I want half the data feeled on pc and other half on PDA. I want something like this. When user enters new records, that unique primary key should be created like autonumber is incrmented by itself when you are entering new record.

[ChipR]

Use a timestamp as a field that is part of the composite primary key, and insert the current time when the record is created. That will prevent duplicates, while allowing you to merge data. As mshmyob pointed out, the autonumber is not necessary in this case.

[mshmyob]

A primary key consisting of just INSPECTORID, COUNTYID, and DATE is all that is needed if the following rules apply.

1. Each inspector may inspect many counties.
2. Each county may be inspected by many inspectors,

with the following constraints

1. Each inspector may inspect the same county many times but not on the same date
2. Many inspectors may inspect the same county on the same date


No need for time field or autonumber.

cheers,

@jinalpatel