If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Field Concatenation in a single table
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! |
#3
|
|||
|
|||
Field Concatenation in a single table
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! |
#4
|
|||
|
|||
Field Concatenation in a single table
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] |
Thread Tools | |
Display Modes | |
|
|