text string unique ID in Access table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database recording analysis results from samples collected from
locations (3 tables). A group of locations at a site will be visited on a
trip (fourth table).
Each sample has a unique ID which historically has been generated in advance
(in Excel) as a text string of SiteName & Location & TripDate. These IDs are
more meaningful than autonumbers.
I am now setting up data input via forms. Is there a way of generating a
text string unique ID rather than using an autonumber? Changing to autonumber
now will be problematic as the database is already populated.

Thanks if you can help.
 
Hi Julia,

See my reply to JH on the message with subject = "Subject: Anybody up for a
challenge":

http://www.microsoft.com/office/com...cess&mid=5ee1dde8-1bdd-4725-9142-70e8ba6e8f06

What you are after is commonly known as an "Intelligent Key".
These IDs are more meaningful than autonumbers.
Click to expand...
In fact, an autonumber (a pseudo key) should be totally meaningless. People
get in trouble with the autonumber data type as soon as they attempt to
assign any meaning to it at all. My recommendation is to go with an
autonumber primary key, which is always hidden from the user. Use a
multi-field unique index on the SiteName, Location & TripDate fields.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a database recording analysis results from samples collected from
locations (3 tables). A group of locations at a site will be visited on a
trip (fourth table).
Each sample has a unique ID which historically has been generated in advance
(in Excel) as a text string of SiteName & Location & TripDate. These IDs are
more meaningful than autonumbers.
I am now setting up data input via forms. Is there a way of generating a
text string unique ID rather than using an autonumber? Changing to autonumber
now will be problematic as the database is already populated.

Thanks if you can help.
 
I have a database recording analysis results from samples collected from
locations (3 tables). A group of locations at a site will be visited on a
trip (fourth table).
Each sample has a unique ID which historically has been generated in advance
(in Excel) as a text string of SiteName & Location & TripDate. These IDs are
more meaningful than autonumbers.
I am now setting up data input via forms. Is there a way of generating a
text string unique ID rather than using an autonumber? Changing to autonumber
now will be problematic as the database is already populated.
Click to expand...

Actually, it wouldn't be at all hard - just add an Autonumber field to
the table and it will self-populate.

But I'd suggest another approach. Storing three fields redundantly in
a fourth is unnecessary and generally not good design. You can set ALL
THREE fields as a joint Primary Key to prevent duplicates (which
concatenating the values would *not* prevent); and you can then
concatenate the three fields for display purposes.

If this table will be related to a lot of other analysis tables, then
perhaps an Autonumber (in addition to a unique three-field index on
these fields) would be useful, just so you don't need to carry all
three fields over to the related tables.

John W. Vinson[MVP]
 
Back
Top