Primary keys

N

Naz

Hi

I've been given an Excel spreadsheet to turn into an Access database, its
very simple spreadsheet and has the following fields

Ref (Primary key WANDS/REF1789/#) # being the next number starting from 1
Fname
Sname
Age
Team

So far the spreadsheet is upto WANDS/REF1789/4578), when i put it into a
table, how can i get every new record to continue the ref and remain unique.

All help is appreciated.
 
P

(PeteCresswell)

Per Naz:
I've been given an Excel spreadsheet to turn into an Access database, its
very simple spreadsheet and has the following fields

Ref (Primary key WANDS/REF1789/#) # being the next number starting from 1
Fname
Sname
Age
Team

So far the spreadsheet is upto WANDS/REF1789/4578)
Click to expand...

Is there any thing special about the "4578" part? i.e. does it
appear on employee ID cards or otherwise get used outside of the
system?

If not, just ditch it and use an MS Access autonumber field.

If so, is it the exact Excel row number?

What is the "WANDS/REF1789" part? Doesn't seem tb a legit part
of a PK if it's always the same.... or are there many sheets with
different "WANDs..." prefixes?

I'm partial to primary keys that have no meaning to anybody and
are never seen or used by the users. For me it makes it
significantly easier to develop new queries and to understand
queries that I haven't seen in a couple of years.

I'll still create unique indexes for fields that I don't want
repeated, but for PK/Joining purposes I like blind dumb numbers.
 
K

Ken Sheridan

Pete:

You might be interested in the following link, which brings together the
discussions on surrogate versus natural keys collated by Joe Celko from the
old CIS CASE forum:


http://community.netscape.com/n/pfx/forum.aspx?msg=19495.1&nav=messages&webtag=ws-msdevapps


While the balance of opinion tends to favour surrogate keys one area where
natural keys do have a real advantage is when using correlated combo boxes on
a continuous form, where a surrogate key requires the use of an inefficient
'hybrid' control made up of a text box superimposed on a combo box, but it
more often than not seems to be the case that a natural key can't be used
anyway in such cases, e.g. if you want to select a State and then a City from
a list restricted to those in the selected State. The city name is not a
candidate key of a Cities table because they are often duplicated, so a
surrogate CityID is needed. For instance there are, I believe, at least 4
Staffords in the USA as well as the original one where I am. The same
applies with personal names of course. And don't do what my doctor's
practice does and use name plus gender plus date-of-birth as a key; I was
once at a hospital clinic where two female patients having exactly the same
names and dates-of-birth were attending simultaneously!

Ken Sheridan
Stafford, England
 
P

(PeteCresswell)

Per Ken Sheridan:
I was
once at a hospital clinic where two female patients having exactly the same
names and dates-of-birth were attending simultaneously!
Click to expand...

Been there with USA's "Social Security Number". Wouldn't use it
as a PK even on a bet.
 
Top