Concatenating fields

[ezuria]

Hi!
How do I concatenate 3 (text)fields into one field and make that field as the primary key for the table?
Thank you so much.
Ezuria

 

[RickSpr]

With your table in Design View, select View>Indexes to open the Indexes dialog window. For the first field, specify an index name of your choosing, select the field from the dropdown list, and set Primary to Yes. Enter the second and third fields on the next two lines, but don't specify an index name on those lines. They will become part of the previous index.

You can also create a multi-field primary key in the table design grid, if they are consecutive. Just highlight all three rows using the row selectors, then click the Primary Key button on the toolbar. Rick Sprague

 

[Guest_imported]

I use a SetValue macro to concatenate multiple fields into one primary key.

1. create your table with 1 primary key
2. Create a form based on the above table
3. Add a text box to concatenate the desired fields
(You can later set its properties to Visable=No
4. Change the text box's name to something more meaningful (TempID)
5. Save and close the form
6. Create a Macro
7. Use the SetValue command
Item=PrimaryKeyName (Your name in Step 1)
Expression=TempID (Step 4)
8. Save Macro (SetID)
9. Open form in Step 5 in design view
10. Change the last field referenced in the concatenated expression property to OnLostFocus=SetID (Macro name in Step 8)

Try you form

 

[JerryDennison]

In deference to our other fine posters I would question the purpose for which you wish to do this. One of the most common mistakes a novice makes is the notion that a primary key should be meaningful. It isn't. It is nothing more than a unique "address" for finding a specific record. The primary key needs to be nothing more than a unique number that the user never even sees or knows exists. The simpler, in terms of db real estate, you keep this key the better your perfromance will be. For example, if the number is set to long integer it is a 32 bit number. This is maximum of 4 bytes. This translates into very fast indexes and searches. In your example(I'm going to assume it concatenates alphanumeric characters), you could end up with a very long value indeed. Since a text field can be up to 255 characters long you could have a field that's 51 times as large as a long integer.