id field

[themase]

Hello

i have a tbl with firstname lastname and various other fields i would like
to create the id field so that is takes i.e 3 charatures from 3 fields to
make the unique id field like the following stemas2202

the second reason for this is to help with duplicate enteries ! (no
duplicates) can some one please shed some light on this please

 

[Becky N]

You will have to create a query based on your table and create a new field to
concantenate the field you want to be all together. So say you want to
combine fields from your table named first name, last name, and ID Number. In
a query, add whatever field you also want to see and then create a new field
of your liking (example, name is UniqueID). You would do this in a blank
column:

Unique ID: [First Name]&[Last Name]&[ID Number]

When you run your query they should all be put together. Hope this helps!

Becky

 

[themase]

thanks great!

You will have to create a query based on your table and create a new field to
concantenate the field you want to be all together. So say you want to
combine fields from your table named first name, last name, and ID Number. In
a query, add whatever field you also want to see and then create a new field
of your liking (example, name is UniqueID). You would do this in a blank
column:

Unique ID: [First Name]&[Last Name]&[ID Number]

When you run your query they should all be put together. Hope this helps!

Becky

Hello

i have a tbl with firstname lastname and various other fields i would like
to create the id field so that is takes i.e 3 charatures from 3 fields to
make the unique id field like the following stemas2202

the second reason for this is to help with duplicate enteries ! (no
duplicates) can some one please shed some light on this please

 

[Jim Bunton]

Sounds doomed to possible failure to me - are you ABSOLUTELY sure that your
approach will ALWAYS create a unique string?

 

[John W. Vinson]

Hello

i have a tbl with firstname lastname and various other fields i would like
to create the id field so that is takes i.e 3 charatures from 3 fields to
make the unique id field like the following stemas2202

This is A Very Bad Idea.

First off, it's not going to work. People can have the same name (my name's
John W. Vinson, and I once worked at a university where there was a Professor
John W. Vinson). Birhdates, addresses, etc. can also duplicate. You'll have to
allow for the need to deduplicate anything you construct.

More importantly, this kind of composite field is redundant. Fields should be
atomic, storing only one piece of information.

Thirdly, you don't NEED to create a composite field to prevent duplicates. See
below.

the second reason for this is to help with duplicate enteries ! (no
duplicates) can some one please shed some light on this please

You can create a unique Index on a combination of up to ten fields. Be sure
they're the *right* fields - as noted above, names are not unique, and even
bringing in other data may not guarantee it. You're really better off using
VBA code in a Form's BeforeUpdate event to *check* for uniqueness, and give
the user the opportunity to determine if these are the same person or two
people who happen to share a name.