Multiple Columns from table into One Column

[Sarnie83]

Hi

I am working with other data that has been created by someone else.

There are a number of columns with the same information in (a serial number). What I need to do is get this into one long column so that I can run other queries from it.

So far I have tried using this SQL:

SELECT A1-TX1 POWER AMPLIFIER 1
FROM SM_Cabinet_T
UNION ALL
SELECT A1-TX1 POWER AMPLIFIER 2
FROM SM_Cabinet_T
UNION ALL
SELECT A1-TX1 POWER AMPLIFIER 3
FROM SM_Cabinet_T
UNION ALL
SELECT A1-TX1 POWER AMPLIFIER 4
FROM SM_Cabinet_T
UNION ALL;

But it is not working - could anyone help me to understand why? Is it to do with the field names or am I entering something incorrectly?

Thanks in Advance!

 

[pr2-eugin]

Hello, for starters the field names have spaces, so you need to enclose them in Square brackets. Like [Should have not used spaces]

Next it is time that you look into normalizing your data. FieldName1, FieldName2,.... FieldNameN are structurally a poor DB design. Since this is your inherited structure I will not tell you off. but would advice you to take necessary action.

Finally, your description, "is not working" is not helping. What is not working, is it bringing wrong data, error in Query, system caught on fire?

 

[Sarnie83]

Thanks for the quick reply Paul.
I have taken the spaces out of the field names and rerun the query.
It comes up with "Syntax error in query. Incomplete query clause"

I am relatively new to Access.....so just for my information, when creating tables, it is better to have no spaces in the field names?

 

[pr2-eugin]

You have one UNION ALL at the end hanging loose (just before the semicolon). Remove that. It should compile alright.

Yes avoid spaces, special chars. Here is a well written guide - http://www.access-programmers.co.uk/forums/showthread.php?t=225837

Hope this helps !

 

[Sarnie83]

Yes thats worked - and thank you so much for the guide!

Thanks

 

[Sarnie83]

Sorry to be a pain again - but is there a way of putting a column next to the query to show which field it came from originally?
So instead of having the fields A1-TX1 POWER AMPLIFIER 1, A1-TX1 POWER AMPLIFIER 2 etc with the serial numbers underneath them. I would like to show it horizontal so that the serial numbers are all in one long column with the original field name next to it as a seperate column?

Thanks

 

[pr2-eugin]

Could you please show some example data using the code tags or using this link http://www.access-programmers.co.uk/forums/showpost.php?p=1373565&postcount=1

I am unable to see what structure you have and what structure you want for your result.

 

[Sarnie83]

SELECT SM_Cabinet_T.UNIQUEIDENTIFIER, SM_Cabinet_T.A1TX1RECEIVER, SM_Cabinet_T.A1TX1DUPLEXERASSEMBLY, SM_Cabinet_T.A1TX1COMBINER, SM_Cabinet_T.A1TX1TRANSMITTERCHASSIS, SM_Cabinet_T.A1TX1POWERAMPLIFIER1, SM_Cabinet_T.A1TX1POWERAMPLIFIER2, SM_Cabinet_T.A1TX1POWERAMPLIFIER3, SM_Cabinet_T.A1TX1POWERAMPLIFIER4
FROM SM_Cabinet_T;

This is the data I am using. All of the fields above contain serial numbers that relate to a number in the UniqueIdentifier column. What I am looking to do is pull all of the serial numbers into one column, against the UniqueIdentifier that relates to that serial number.

What I'd also like to know if you can do on a SQL statement, is add a field in the query that shows which column the serial number originally came from. I'm not sure if this is something you can actually do though......

 

[Galaxiom]

SELECT [A1-TX1 POWER AMPLIFIER 1], 1 AS sourcefield
FROM SM_Cabinet_T
UNION ALL
SELECT [A1-TX1 POWER AMPLIFIER 2], 2
FROM SM_Cabinet_T
 
etc