Primary keys

[Guest]

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.

 

[Rick Brandt]

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.

If in fact that field in all records will always start with " WANDS/REF1789/"
then those characters do not need to actually be stored at all. You should just
store the incrementing number and use expressions or formatting to *display* the
other characters on your forms and reports.

With that field being a simple number then there are various ways to cause new
records that you add to use the next number in the sequence. The most common is
to use DMax() to find the highest existing value and then add 1 to that as new
records are saved.

While that "could" be done while storing your current long text value it would
be far less efficient as you would need to parse the text in all rows so that
just that last numeric value on the end can be examined to find the highest
existing value.

By storing a separate numeric value with an index (and you can specify that the
index be built in descending value order) it will be very efficient since the
retrieved "max" value will always be the very first value in the index.

Even if you will not always have the same "WANDS/REF1789/" values it would be
better to have each "piece" stored in separate fields rather then cramming them
all into a combined field.

 

[(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)

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.

 

[Guest]

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

 

[(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!

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