Concatenating three fields into 1

[MC2Much]

I am trying to combine three fields (PartNumber, SerialNumber and
ModelNumber) into a fourth field UniqueID from the same table table 1. what
is the simplest way to go about this?

 

[RoyVidar]

I am trying to combine three fields (PartNumber, SerialNumber and
ModelNumber) into a fourth field UniqueID from the same table table
1. what is the simplest way to go about this?

The best way - is simply not doing it!

If you wish to use those three fields as a natural primary key to the
table, then in design view, select all three fields, then hit the
primary key button in the toolbar.

 

[Brian]

If the users just need to see the combination on a form or report (see my
notes at the bottom about actually storing values), simply enter this in the
Field in a query:

PartSerialModel: [PartNumber] & "-" & [SerialNumber] & "-" & [ModelNumber]

That will make this combination:

PartNumber: 123ERT67
SerialNumber: 3219518Y987
ModelNumber: T67Q

look like this:

123ERT67-3219518Y987-T67Q

You could leave out the dashes, but then the users would have a hard time
determining where each segment ends and the next begins.

However, I suspect there is a deeper issue and better answer here. If you
want a STORABLE unique key, just make all three combined the primary key in
the table so that there can be only one entry for any particular combination
of Part, Serial & Model. To do this, select all three before you click the
key button on the toolbar in the table's design view. No need to tinker with
concatenating them at all.

Avoid storing information twice - once as individual elements, then again in
some combined form.

 

[FBrnstrmr]

I have asked this question in different ways and the answer everywhere seems
to be to not place them together but to make all three (in my case) as
primary keys. The problem is that as indiviuald entities they are not unique,
it is only a unique record when the three fields are concatenated. I can't
see any other solution than to export to a form (or other vehicle ) which
concatenates them and then transfer them back into the original dbase is
there another solution?
Thanks very much.
-F

 

[Jeff Boyce]

Open the table in design view.

Open the index window.

Type a name for this unique index ... on the same line select the first of
the three fields that, together, need to be unique.

On the second line, don't put any name, just select the second field.

Repeat for the third field on the third line.

Highlight the first line again and set it to Primary Key.

You've just created a primary key that spans the three fields.

(or have I misunderstood what you are trying to do?)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[RoyVidar]

I have asked this question in different ways and the answer
everywhere seems to be to not place them together but to make all
three (in my case) as primary keys. The problem is that as
indiviuald entities they are not unique, it is only a unique record
when the three fields are concatenated. I can't see any other
solution than to export to a form (or other vehicle ) which
concatenates them and then transfer them back into the original dbase
is there another solution? Thanks very much.
-F

I don't understand

How is a concatenated monsterfield with the information

FooBarWombat

unique while the combination

Foo Bar Wombat

with each piece of information in it's dedicated field is not?

And no, you do not make all three primary keys (plural), a table
can only have one primary key (singular), but that primary key can
consist of more than one field.

Such construct means that the combination of the three fields must
be unique, so in addition to the above, you can have

Bah Bar Wombat
Foo Zoo Wombat
Foo Bar Guzunder

but not another

Foo Bar Wombat

To do the whole process, select the three fields in design view of
the table, then hit the Primary Key button on the toolbar.

Test it out, for intance with these fantastic test values ;-)

 

[FBrnstrmr]

Thanks Roy, I had done that but the message is that I cannot have a null
entry so I now have to populate one of the fields with zeros wherever there
are nulls. Tha main prob is that I am adding info from my departement to info
already used by others so I am not starting from scratch. The null fields
will be filled as tiime goes on but in the meantime will have to be set at
zeros to get the program to work as required. Not sure how, as there are at
present 34000+ records. Once filled it may work, I recall getting a diff.
message the first time I tried, but perhaps not. (Ihave had a lot of error
messages of late.lol. Thanks very much.
-F

 

[John W. Vinson]

Thanks Roy, I had done that but the message is that I cannot have a null
entry so I now have to populate one of the fields with zeros wherever there
are nulls. Tha main prob is that I am adding info from my departement to info
already used by others so I am not starting from scratch. The null fields
will be filled as tiime goes on but in the meantime will have to be set at
zeros to get the program to work as required. Not sure how, as there are at
present 34000+ records. Once filled it may work, I recall getting a diff.
message the first time I tried, but perhaps not. (Ihave had a lot of error
messages of late.lol. Thanks very much.

A Primary Key does indeed require non-null entries. If inserting zeroes would
still leave the triples unique, you can easily do so using an Update Query.
Create a query based on the table; put a criterion of

IS NULL

on one of these fields; change the query to an Update query using the Query
menu option or the query type tool on the toolbar; and put 0 on the "Update
To" line. Run the query by clicking the ! icon. Repeat for the other two
fields.

It is possible to have a unique three (or ten for that matter!) field index
without that index being the Primary Key. Such an index can be set to ignore
NULL values; to create such an index see Jeff's response in this thread.

 

[FBrnstrmr]

I put zeros in the field that was null and checked to see if each of my three
fields were unique. None were. And afaik the combination may or may not be. I
will have to run a query or something to find dups but there are 34508
records, couls be a prob.
Thanks for all the help.
-F

 

[FBrnstrmr]

I just did the indexing thing and it appears from the message that each field
needs to be unique in order to work. Just having the comination being unique
as a group will not do. As far as I can tell. So I will try to concatenate
the fields and place them into the database as a single field.

 

[John W. Vinson]

I just did the indexing thing and it appears from the message that each field
needs to be unique in order to work. Just having the comination being unique
as a group will not do. As far as I can tell. So I will try to concatenate
the fields and place them into the database as a single field.

If the three fields are not unique - *then the concatenation will not be
unique either*.

Think about it: if you have two records with

1; 74; 225
1; 74; 225

then concatenating them into

001074225
001074225

won't change ANYTHING.
STOP.

You're making a mistake.

Did you create *three unique indexes*, one on each field? Then that was your
mistake.

Or did you create *one* index incorporating the three fields?

Reread Jeff's suggestion. I'm reposting it (thanks, Jeff) just in case you
didn't get it.

======================
Open the table in design view.

Open the index window.

Type a name for this unique index ... on the same line select the first of
the three fields that, together, need to be unique.

On the second line, don't put any name, just select the second field.

Repeat for the third field on the third line.

Highlight the first line again and set it to Primary Key.

You've just created a primary key that spans the three fields.

(or have I misunderstood what you are trying to do?)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
========================