Field Concatenation in a single table

I have imported 160,000 records from an Excel file into an Access table
called "Vendor Master". The Vendor Master table has 3 fields, all text, that
I would like to combine into 1 field to be used as a key field.

I created a new field called "VIN" to combine the 3 other fields, but can't
seem to get the expression right......... if in fact I should be even using
an expression?

GIVEN INFO: Database Name - CSU
Table Name - Vendor Master
Field#1 Name - PRE
Field#2 Name - FEID
Field#3 Name - EXT

My failed expression in default value: =[Vendor Master]![PRE]&+[Vendor
Master]![FEID]&+[Vendor Master]![EXT]

I keep getting an error message saying that Access can't find my field
names? Where did I go wrong?

HELP! HELP! HELP! HELP!

If they have individual meaning then keep them separate and only combine when
needed for display in a query, form, or report.
To concatenate in a query design view use this --
VIN: [PRE] & [FEID] & [EXT]

In query SQL use this --
[Vendor Master].[PRE] & [Vendor Master].[FEID] & [Vendor Master].[EXT] AS VIN

--
KARL DEWEY
Build a little - Test a little


"Mr. Beginner" wrote:

I have imported 160,000 records from an Excel file into an Access table
called "Vendor Master". The Vendor Master table has 3 fields, all text, that
I would like to combine into 1 field to be used as a key field.

I created a new field called "VIN" to combine the 3 other fields, but can't
seem to get the expression right......... if in fact I should be even using
an expression?

GIVEN INFO: Database Name - CSU
Table Name - Vendor Master
Field#1 Name - PRE
Field#2 Name - FEID
Field#3 Name - EXT

My failed expression in default value: =[Vendor Master]![PRE]&+[Vendor
Master]![FEID]&+[Vendor Master]![EXT]

I keep getting an error message saying that Access can't find my field
names? Where did I go wrong?

HELP! HELP! HELP! HELP!

1. Import the Excel spreadsheet into a new table.
2. Select Create New Query.
3. Show the new table as the source for your new query. (In the blank area
above the horizontal
scroll bar, right-click, click on "Show Table", select the new table.
4. Click on "Make Table" icon. You will be prompted to supply a table name.
5. In the "Field" space in the query type the following: VIN: [field1] &
[field2] & [field3]
(Where [field1] and [field2] and [field3] are the names of the fields
in the new table that you wish
to concatenate).
6. This will create a new table with one field named VIN.



Mr. Beginner wrote:
I have imported 160,000 records from an Excel file into an Access table
called "Vendor Master". The Vendor Master table has 3 fields, all text, that
I would like to combine into 1 field to be used as a key field.

I created a new field called "VIN" to combine the 3 other fields, but can't
seem to get the expression right......... if in fact I should be even using
an expression?

GIVEN INFO: Database Name - CSU
Table Name - Vendor Master
Field#1 Name - PRE
Field#2 Name - FEID
Field#3 Name - EXT

My failed expression in default value: =[Vendor Master]![PRE]&+[Vendor
Master]![FEID]&+[Vendor Master]![EXT]

I keep getting an error message saying that Access can't find my field
names? Where did I go wrong?

HELP! HELP! HELP! HELP!

On Wed, 10 Sep 2008 08:55:02 -0700, Mr. Beginner
wrote:

I have imported 160,000 records from an Excel file into an Access table
called "Vendor Master". The Vendor Master table has 3 fields, all text, that
I would like to combine into 1 field to be used as a key field.

It is neither necessary nor desirable to do so. A Primary Key can consist of
one field - or *ten* fields if necessary!

Open the table in design view; ctrl-click these three fields; and click the
Key icon. This will make the three fields a joint Primary Key; you will be
allowed duplicates in any one or two of the fields but no records which are
duplicates in all three.

You can concatenate the fields *for display purposes* in a query with a syntax
like

VIN: [PRE] & [FEID] & [EXT]

The ! syntax is appropriate for form objects (. is the delimiter between
tablenames and fieldnames), and if the fieldnames are all in the same table
you don't need to specify the tablename at all; in addition, you were using
both the & and + operators - either one is a concatenation operator but you
don't need both.

--

John W. Vinson [MVP]