Stacking Columns

Hi,

I've got the following problem.

I've got a table with multiple columns next to eachother, as in:

5000 | AL | AP | AQ | AL | AP

Basicly i want to create a query where it stacks all those columns
(AL,AP,AQ,AL,AP) on top of eachother in one column. This so i can use this
column with a 'total' to get rid of the doubles, then process this column
further.

How would i get this done?

Thanks for helping,
Gunti

By "columns", do you mean fields or field names? What do you mean by "stack"?

A WAG suggests you want to normalize your table with a union query but I
could be very wrong. Can you provide table and field names as well as a
couple sample records and desired display in your query?

--
Duane Hookom
Microsoft Access MVP


"Gunti" wrote:

Hi,

I've got the following problem.

I've got a table with multiple columns next to eachother, as in:

5000 | AL | AP | AQ | AL | AP

Basicly i want to create a query where it stacks all those columns
(AL,AP,AQ,AL,AP) on top of eachother in one column. This so i can use this
column with a 'total' to get rid of the doubles, then process this column
further.

How would i get this done?

Thanks for helping,
Gunti

I basicly have the following 'record' in access:

Field 1, Field 2, Field 3, Field 4, Field 5
AS AS AF AG AF
Multiple records

I want Access to return the following in a query:

Record 1 AS
Record 2 AS
Record 3 AF
Record 4 AG
Record 5 AF

Then i want Access to automaticly filter the duplicates which would return in:

Record 1 AS
Record 2 AF
Record 3 AG

Thanks for your time,
Gunti

"Duane Hookom" wrote:

By "columns", do you mean fields or field names? What do you mean by "stack"?

A WAG suggests you want to normalize your table with a union query but I
could be very wrong. Can you provide table and field names as well as a
couple sample records and desired display in your query?

--
Duane Hookom
Microsoft Access MVP


"Gunti" wrote:

Hi,

I've got the following problem.

I've got a table with multiple columns next to eachother, as in:

5000 | AL | AP | AQ | AL | AP

Basicly i want to create a query where it stacks all those columns
(AL,AP,AQ,AL,AP) on top of eachother in one column. This so i can use this
column with a 'total' to get rid of the doubles, then process this column
further.

How would i get this done?

Thanks for helping,
Gunti

Use a UNION query.

SELECT Field1
FROM SomeTable
UNION
SELECT Field2
FROM SomeTable
UNION
SELECT Field3
FROM SomeTable
UNION
SELECT Field4
FROM SomeTable
UNION
SELECT Field5
FROM SomeTable

That will return all the unique values in the fields. UNION queries can only
be built in SQL view and not in the Query design view (the GUI tool).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Gunti wrote:
I basicly have the following 'record' in access:

Field 1, Field 2, Field 3, Field 4, Field 5
AS AS AF AG AF
Multiple records

I want Access to return the following in a query:

Record 1 AS
Record 2 AS
Record 3 AF
Record 4 AG
Record 5 AF

Then i want Access to automaticly filter the duplicates which would return in:

Record 1 AS
Record 2 AF
Record 3 AG

Thanks for your time,
Gunti

"Duane Hookom" wrote:

By "columns", do you mean fields or field names? What do you mean by "stack"?

A WAG suggests you want to normalize your table with a union query but I
could be very wrong. Can you provide table and field names as well as a
couple sample records and desired display in your query?

--
Duane Hookom
Microsoft Access MVP


"Gunti" wrote:

Hi,

I've got the following problem.

I've got a table with multiple columns next to eachother, as in:

5000 | AL | AP | AQ | AL | AP

Basicly i want to create a query where it stacks all those columns
(AL,AP,AQ,AL,AP) on top of eachother in one column. This so i can use this
column with a 'total' to get rid of the doubles, then process this column
further.

How would i get this done?

Thanks for helping,
Gunti

Thanks alot, you can't believe how much you've helped me

Gunti

"John Spencer" wrote:

Use a UNION query.

SELECT Field1
FROM SomeTable
UNION
SELECT Field2
FROM SomeTable
UNION
SELECT Field3
FROM SomeTable
UNION
SELECT Field4
FROM SomeTable
UNION
SELECT Field5
FROM SomeTable

That will return all the unique values in the fields. UNION queries can only
be built in SQL view and not in the Query design view (the GUI tool).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Gunti wrote:
I basicly have the following 'record' in access:

Field 1, Field 2, Field 3, Field 4, Field 5
AS AS AF AG AF
Multiple records

I want Access to return the following in a query:

Record 1 AS
Record 2 AS
Record 3 AF
Record 4 AG
Record 5 AF

Then i want Access to automaticly filter the duplicates which would return in:

Record 1 AS
Record 2 AF
Record 3 AG

Thanks for your time,
Gunti

"Duane Hookom" wrote:

By "columns", do you mean fields or field names? What do you mean by "stack"?

A WAG suggests you want to normalize your table with a union query but I
could be very wrong. Can you provide table and field names as well as a
couple sample records and desired display in your query?

--
Duane Hookom
Microsoft Access MVP


"Gunti" wrote:

Hi,

I've got the following problem.

I've got a table with multiple columns next to eachother, as in:

5000 | AL | AP | AQ | AL | AP

Basicly i want to create a query where it stacks all those columns
(AL,AP,AQ,AL,AP) on top of eachother in one column. This so i can use this
column with a 'total' to get rid of the doubles, then process this column
further.

How would i get this done?

Thanks for helping,
Gunti
.