Combining some colums in an Access Table

[cswillard]

I have a table in Access with 184,000 rows or records of data and 20 columns or field names. When looking at the table ten of the columns represent the identifying information for the data stored in the other ten columns. I need to convert the last ten columns of data into one column with a second column added to identify the original field name. Then end result would be one current record of data being converted into ten records with the original 10 columns of identifying data, a new column containing the original field name and a column for the data value from the original column identified in the new column.



I have been able to do this in Excel using a consolidation pivot table, un-checking the rows and columns then clicking on the final sum value to get a detailed list of the records. Unfortunately there still appears to be a cap because I get a message saying some of the data has been dropped after a little over a million rows.

I am using the 2010 version of Access and Excel.

Any suggestions on how I can easily accomplish this would be appreciated. This is the smaller table. I have several others that have up to 200 columns that need to be converted the same way.

[Robeen]

In a query, you can combine several columns into one column:
Select Field1 + ' - ' + Field2 + ' - ' + Field3 . . . As NewFieldName
From TableName;

[cswillard]

Thank you for the reply Robeen. I tried the above and get an eror mesaage in the new column. I tried it with just the + sign and did get the toal of the 3 columns I tested. I also tried the & sign and received one long string of the 3 numbers in the three test columns. I am assuming the direction should give me a columnthat would look somethign like 4 - 10 - 2 with each nubmer being in one of the three original columns. I need it two create a new row for each value in each a column.

Here is a sample of what I have and what I am trying to accomplish.

	Current
	field 1	field 2	field 3	field 4	field 5	field 6
Row 1	MA	25	4	cou	2	5
Row 2	MA	26	4	cou	9	11
Row 3	MA	27	2	sta	12	15
Row 4	MA	28	2	sta	3	18
	Desired result
	field 1	field 2	field 3	field 4	New field 5	New Field 6
Row 1	MA	25	4	cou	field 5	2
Row 2	MA	26	4	cou	field 5	9
Row 3	MA	27	2	sta	field 5	12
Row 4	MA	28	2	sta	field 5	3
Row 5	MA	25	4	cou	field 6	5
Row 6	MA	26	4	cou	field 6	11
Row 7	MA	27	2	sta	field 6	15
Row 8	MA	28	2	sta	field 6	18

[Robeen]

My apologies. It needed to be '&' . . . and not '+'.
At work I use another SQL-like program in which '+' is the concatenation character.
Sorry.

As far as trying to split one row of data into multiple rows:
Can you put your data that you showed above into a Table for us?
When you reply, click 'Go Advanced' . . . on the top of the advanced message space - there is an option to add a Table to your message.

[cswillard]

Current Table Structure

	field 1	field 2	field 3	field 4	field 5	field 6
Row 1	MA	25	4	cou	2	5
Row 2	MA	26	4	cou	9	11
Row 3	MA	27	2	sta	12	15
Row 4	MA	28	2	sta	3	18

[cswillard]

	Desired result				old column name becomes data in the row
	field 1	field 2	field 3	field 4	New field 5	New Field 6
Row 1	MA	25	4	cou	field 5	2
Row 2	MA	26	4	cou	field 5	9
Row 3	MA	27	2	sta	field 5	12
Row 4	MA	28	2	sta	field 5	3
Row 5	MA	25	4	cou	field 6	5
Row 6	MA	26	4	cou	field 6	11 Bottom of Form
Row 7	MA	27	2	sta	field 6	15
Row 8	MA	28	2	sta	field 6	18

[cswillard]

The attached spreadsheet has the two samples above in a cleaning view.