Concatenation for one field from two others in same table

[Dale]

Is it possible to make a concatenation of a 3rd field from fields 1 and 2 all
in the same table? If so, can I make that 3rd field the primary key?

 

[boblarson]

1. Save yourself the hassle of trying to create a primary key and let Access
do it. It is best to let the system deal with primary keys and not get into
the business of trying to tell Access how to do its business.

2. You can always DISPLAY the concatenated fields in a query or on a form or
report.

 

[John W. Vinson]

Is it possible to make a concatenation of a 3rd field from fields 1 and 2 all
in the same table? If so, can I make that 3rd field the primary key?

No, and no. Or at least you needn't and shouldn't.

You can concatenate two fields from a table *IN A QUERY* for display purposes:

ConcatenatedField: [FirstField] & [SecondField]

A Primary Key can consist of up to TEN fields; open the table in design view,
ctrl-click the two (or more!) fields (they should darken to indicate that
they've been selected). Then click the "key" icon on the toolbar. The fields
will then be a joint primary key; either field will allow duplicates, but you
won't be allowed to enter any records with duplicates of both fields.

 

[boblarson]

--
Bob Larson
Free MS Access Tutorials and Samples at http://www.btabdevelopment.com

__________________________________
However, it is not necessarily a good thing to use a composite key as it can
be very difficult for novice users to set up the "foreign key" equivalent in
their other tables. I personally would just say - go for the autonumber
primary key and then use validation to ensure no duplicates. But that's me.

Is it possible to make a concatenation of a 3rd field from fields 1 and 2 all
in the same table? If so, can I make that 3rd field the primary key?

No, and no. Or at least you needn't and shouldn't.

You can concatenate two fields from a table *IN A QUERY* for display purposes:

ConcatenatedField: [FirstField] & [SecondField]

A Primary Key can consist of up to TEN fields; open the table in design view,
ctrl-click the two (or more!) fields (they should darken to indicate that
they've been selected). Then click the "key" icon on the toolbar. The fields
will then be a joint primary key; either field will allow duplicates, but you
won't be allowed to enter any records with duplicates of both fields.

 

[John W. Vinson]

However, it is not necessarily a good thing to use a composite key as it can
be very difficult for novice users to set up the "foreign key" equivalent in
their other tables. I personally would just say - go for the autonumber
primary key and then use validation to ensure no duplicates. But that's me.

Validation, or a separate unique two-field index. Agreed about the foreign
keys!

 

[boblarson]

Okay, now I have it. It is best to use an autonumber primary key and then
create a multi-field INDEX on the field you don't want duplicated.

I will post back soon with a URL to go see how to create that multi-field
index (in screenshots) so that it is easy to understand. An explanation here
might be too confusing.

 

[boblarson]

Here - this should help
http://www.btabdevelopment.com/main...createamultifieldindex/tabid/140/Default.aspx

 

[Dale]

Thanks to Bob & John for your responses. I was hoping to accomplish this:
Use the client bill to ID (12AMR1234 as an example; Field 1) and the
individual job site sequence number (001 - many to one relationships; Field
2) to combine a unique client ID for use in access and set up as the primary
key (12AMR1234-001; 12AMR1234-002, 12AMR1234-003, etc.; Field 3).

I'm downloading the client files from a older DOS system (that still is the
primary business system running our business) into .xls and then importing it
as my base customer file in Access. It contains basic client information
along with those 2 identifying fields mentioned above to utilize as unique
identifiers to each client. Access will provide my team a more robust CRM
solution that the DOS cannot once the database is built.

If I let Access assign the primary key (1,2,3,4,etc. through an autonumber
choice) then the next time I download from the DOS system(weekly), I won't
maintain integrity because new clients will have been added from DOS - I.E.
what was once 2 might now be 4 so the CRM data entered won't match the right
client.

 

[John W. Vinson]

If I let Access assign the primary key (1,2,3,4,etc. through an autonumber
choice) then the next time I download from the DOS system(weekly), I won't
maintain integrity because new clients will have been added from DOS - I.E.
what was once 2 might now be 4 so the CRM data entered won't match the right
client.

How is the client information identified in your downloaded file? Could you
post some (real or valid-but-fake) sample data?

If the program is identifying clients by name, does it have some provision for
the case where you might have two clients both named Bob Smith?

 

[David W. Fenton]

It is best to use an autonumber primary key and then
create a multi-field INDEX on the field you don't want duplicated.

Of course, this will only work if there are no Nulls in any of the
fields in your multi-field index. Otherwise, "duplicates" will be
allowed, since Null means "unknown" -- Jet sees every unknown value
as unique, even though humans beings seem them all as identical. The
only way to avoid this is to have a default value in any field that
could be Null, and that leads to almost as many problems as it
fixes.

This is one of the many reasons why natural keys, whether used as
the actual PK or simply to enforce uniqueness, are so problematic --
it almost always forces you to do some level of validation in your
application. Once you need to do that, the surrogate key becomes the
easier implementation.

 

[Dale]

Thanks John - Here's a sampling ...

Cust ID Seq Name Address
12CIT4982 000 Citi Bank Bill To Location
12CIT4982 001 Citi Bank Branch Location #1
12CIT4982 002 Citi Bank Branch Location #2

The Customer ID (12CIT4982) is one column of data, Sequence 000 another,
Name another and address another in the .xls export from the DOS system.