Getting multiple columns in a table into one column

[pfiroz]

Hi All
I have an issue that seem like it would be an easy thing to do but not sure how. I just basically need to get three columns in a table into one column to I can do a query on that one column. But before I do the query I need all the data from the three columns.
Whats the best way to do this.
Appreciate any help!!

 

[HiTechCoach]

May try a calculated field.

In the Query Designer:
MyNewField: [Field1] & [Field2] & [Field3]

or the SQL:
[Field1] & [Field2] & [Field3] as MyNewField

 

[pbaldy]

And if you meant as separate records:

SELECT Field1
FROM TableName
UNION ALL
SELECT Field2
FROM TableName
UNION ALL
SELECT Field3
FROM TableName

 

[pfiroz]

Hi,
Thanks for the reply. Actually that will just concatenate the fields. That's not what I need though. I need to stack the columns into one column. so if there are 10 observations in column 1 to column 3 then the stacked column would need to have 30 obs.

 

[pfiroz]

Oh I just saw the last response. Ya thats exactly what I need.
Thanks!!

 

[pfiroz]

Hi,
I'm trying to make this work. How do I implement this within a query? The table name is tblFinancialConnections and the three columns to be stacked are Donantions1 , Donations2, and Donations3. I started building a query and pulled those columns into the grid but where to I put the sql text to add the fourth field that will stack these three ?
Appreciate any help!!

 

[pbaldy]

That is a query, though UNION queries can not be represented in the design grid, so you'll have to switch to SQL view to create it (you can start with the design grid for the first field, then switch).

 

[pfiroz]

Hey,
Hmm well I tried that buy am doing something wrong.
I put the code below in the sql window but when I try to run it, it asks for the parameter value tblFinancialConnections.Donations1 etc..
But I have a table called tblFinancialConnections and a field called Donations1. I double checked ??

SELECT tblFinancialConnections.Donations1
FROM tblFinancialConnections
UNION ALL
SELECT tblFinancialConnections.Donations2
FROM tblFinancialConnections
UNION ALL
SELECT tblFinancialConnections.Donations3
FROM tblFinancialConnections;

 

[pfiroz]

Oh Never mind..I'm an idiot lol
The 3 columns are called Donation1 , Donation2 , and Donation3
NOT Donations1, Donations2, Donations3

Thanks!

 

[pbaldy]

That SQL looks fine. Can you post the db, or a sample of it? The error is usually because of a misspelled table or field name.

 

[pbaldy]

Ah, glad you have it working. I should mention that fields like that are a normalization problem, so you may want to search on "normalization".