Table-data transformation

  • Thread starter Thread starter Touche' Techie
  • Start date Start date
T

Touche' Techie

I would like to take data from 3 different columns of data and recombine to a
different format in another column.
The original data is a series of numerical and text codes.
first column, second column, third column
12,30,1245CF
I would like the new column to contain the data in this form:
12-30-1245CF
Can this transformation be automated? Can I set up a form to just collect
the individual numbers and make the table transform them to this new form and
unique identifier (key) ??? Or perhaps the form transforms and puts the data
in the table. I need the data in both formats and don't want to double enter
the info...
 
=?Utf-8?B?VG91Y2hlJyBUZWNoaWU=?= <Touche'
(e-mail address removed)> wrote in
I would like to take data from 3 different columns of data and
recombine to a different format in another column.
The original data is a series of numerical and text codes.
first column, second column, third column
12,30,1245CF
I would like the new column to contain the data in this form:
12-30-1245CF
Can this transformation be automated? Can I set up a form to just
collect the individual numbers and make the table transform them
to this new form and unique identifier (key) ??? Or perhaps the
form transforms and puts the data in the table. I need the data in
both formats and don't want to double enter the info...
Click to expand...
leave the table exactly as it is. Use queries to simply combine the
three fields when you need the combined version.

In a blank column of the query builder, just put
newKey: [first field] & "-" & [second field] & "-" & third field.

As to unique key, in table design view, create a composite index, by
clicking the indexes icon, type a name for the indexon the topmost
blank row of the index design tool, select your first field in the
field name column, leave the sort order set to Ascending. In the row
immediately below the one you just created, leave the name column
blank, select the second field and in the row below, select the
third field. Select the first row, and set the unique property to
Yes, and optionally the primary property to yes. close the index
tool, save the table. Done.
 
Use a query:

SELECT Field1, Field2, Field3,
Field1 & "-" & Field2 & "-" & Field3 AS All
FROM YourTable;
 
Back
Top