Solved: query to transpose records from several fields into 1 field

[nat1]

Hi,

I have a table with 13 fields any many records. Is it possible in Access to transpose the records from fields3 to 13 into field2 below the existing records? I also require that for each field that is transposed the records from field1 are copied.

Any help you can give me greatly appreciated.

[XLGibbs]

I don't think transpose is the right word.

You can do this using an Insert Query or Insert statement

INSERT INTO Table1 (FIELD1,FIELD2)
Select "Field1Value",FIELD3 FROM Table1 UNION ALL
Select "Field1Value",FIELD4 FROM Table1 UNION ALL
....etc
Select "Field1Value",FIELD13 FROM Table1

Presuming "Field1Value" would be the same for every row.

[nat1]

XLGibbs,

Thank-you so much for your help!! I just couldn't think of a way of doing this, my access knowledge at the moment is limited.

Regards

[XLGibbs]

No problem. What i gave you can by typed into the SQL view of a Query designer, or you can create an insert query and run separately for each column 3 - 13 using the query designer.

[nat1]

XLGibbs,

Thanks once again for the sql. When I try to execute this query I receive the error:syntax error in FROM clause. I have tried to figure out what might be the cause without much success!

Would it be possible to modify this query so that 2 new fields are also created between fields 1 and 2?. The values in these fields would be the same for each record i.e 0, P.

Thanks for your help!

[XLGibbs]

Post exactly what you typed in so I can take a look.

I am not sure, but Access may not accept UNION ALL in it's SQL. It does accept just UNION however...it might be that. The union allows you to merge all of the select statements "as one" without having to type Insert Into (...) etc each time.


For new columns, add them to your table and then specify in the INSERT

Insert Into (Col1,col2,col3)
Select a,b,c FROM [Table]

[nat1]

XLGibbs,

Thank-you for your quick response!! Here is the sql:
INSERT INTO Mode_Copy (FMEAID,M1)
SELECT FMEAID,C1
FROM Mode
UNION ALL
SELECT FMEAID,LE1
FROM Mode
UNION ALL
SELECT FMEAID,NE1
FROM Mode
UNION ALL
SELECT FMEAID,EE1
FROM Mode
UNION ALL
SELECT FMEAID,D1
FROM Mode
UNION ALL
SELECT FMEAID,P1
FROM Mode
UNION ALL
SELECT FMEAID,R1
FROM Mode;
I tried removing ALL but i receive the same error. I have looked at Microsoft help, but just can't figure out what I'm doing wrong. The syntax complies with that shown in help!

Thanks for helping me out!

[XLGibbs]

Are FMEAID and M1 column names? If they are values you must have them quotes.

Have you tried it with just one Insert ?
INSERT INTO Mode_Copy (FMEAID,M1)
SELECT FMEAID,C1
FROM Mode

[XLGibbs]

Are making sure to change the query type to SQL SPECIFIC. That is a critical step I omitted.

In a new query, do not add tables. From the toolbar open the query sub menu and scroll down (or expand the list) to see SQL SPECIFIC. Under there is a "Union Query". Just past the code you have above into that and it will work.

[nat1]

FMEAID and M1 are column names. I tried just one insert and now receive the error:syntax error in INSERT INTO statement.

Thanks

[nat1]

Oh ok I will give that a try.

[XLGibbs]

That should do it, if you paste your statement into a SQL Specific Union query. My fault for not explaining how to get to the write SQL window (it is different than just going to SQL view in normal design mode)...

Sorry about the confusion on that...

[nat1]

Please you do not need to apologise, I'm very grateful for your help.

I tried the code in an sql-specific query and I get the error:syntax error in INSERT INTO statement. I'm sure it is something I'm doing wrong but cannot for the life of me figure out what?

[XLGibbs]

Try to do just one INSERT (with no Unions or extra things) and see if it works.

The syntax looks correct to me. If this is a one time thing, you can also try doing an APPEND query which can be done in the query designer.

Create a new query in design view. Bring the source table in, and change the query type to APPEND. It will ask what table you want to append to (in this case it is the same thing.

You will then see options in the query design grid for APPEND FROM and APPEND TO columns


You will have to do it for each of your inserts separately, but it will work.

[nat1]

Using just one INSERT in an append query works. I cannot get the union query working, but as you say the syntax is correct, so not sure what is wrong here?.

I will have to create a query for each column, a little more time consuming but it does work.

Thank-you for your help, I am very grateful.

[XLGibbs]

Tough to say what is wrong. I don't use Access that much, but I do know that their error messages are cryptic at best.

Unless access requires you to alias the source column AS the destination column...but I don't think it requires that.

Insert into Table (Col1, col2)
Select Col1,Col3 AS Col2
Union
Select COl1,Col4 AS Col2

I don't think that is it, really not sure but i could just as easily be missing something simple too.