Combining Values in Different Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need an Access query to convert data from this format:

key flag
user1 A
user1 B
user1 C

to this format:

key flag
user1 A,B,C

I can do it crudely in sql (for sql server), but it takes several temp table
steps and uses the case statement.

Thanks for any help.
 
I've seen almost identical posts in this newsgroup many many many times in
the last few weeks. Did you try searching for your answer first?

Rick B
 
Sorry for lookiing stupid. I searched for case statements, but none answered
exactly what I needed. What should I search for exactly?
 
and one more...


Thanks.

It works.

Michel Walsh said:
Hi,


Definitively, you then fall back on more standard ground. You first rank
each values ( 380 is first, 609 is 2nd and 610 is 3rd), and then, pivot on
that rank.


To rank data, a possible technique is to use a GROUP BY on a join:


----------------------------------------------------
SELECT a.value, COUNT(*) As rank

FROM myTable As a INNER JOIN myTable as b
ON a.value <= b.value

GROUP BY a.value
---------------------------------------------------


save it as, let say, q1. The crosstab is then


-------------------------------------------------
TRANSFORM Last(value)
SELECT null
FROM q1
GROUP BY 1
PIVOT rank
------------------------------------------------


Hoping it may help,
Vanderghast, Access MVP





MK said:
Love this query !

But ,

If the data is 380
609
611

The results are Coulmns named Expr1, 380,609, 611
Containing null, 380,609,611

Is there anyway to standardize the column names regardless of the data
i.e. Columns named Expr1, COL1, COL2, COL3
Conataining null, 380,609,611

Thanks,

Mike

Michel Walsh said:
Hi,


Sounds like a very special crosstab case:

PARAMETERS Country Text ( 255 );
TRANSFORM LAST(number)
SELECT null
FROM ( SELECT Count(1SEs.SEAccNumber) AS Number
FROM 1SEs
WHERE 1SEs.Country=Country
GROUP BY 1SEs.SignMonth ) As x
GROUP BY 1
PIVOT number




If you have to open a recordset on your actual query, another solution is
to
use GetRows() on the recordset.... GetRows( ) transpose the data in a
tableau of variants.


Hoping it may help,
Vanderghast, Access MVP

message
Hi,

I am trying to change queries that produce one vertical column (field)
of
results and many rows (records) into one that is transposed to be one
horizontal row. Is there any simple way / function to do this?

E.g. my parameter query below.

PARAMETERS Country Text ( 255 );
SELECT Count([1SEs].[SEAccNumber]) AS [Number]
FROM 1SEs
WHERE ((([1SEs].Country)=[Country]))
GROUP BY [1SEs].SignMonth;

.produces.

1150
1200
1644
1429

but I need to produce

1150 1200 1644 1429

so that I can use this sort of query and VBA to CopyfromRecordset
straight
into Excel spreadsheet rows.

Can this be done?

Many thanks in advance.

Robert
Click to expand...
Click to expand...
Click to expand...
 
Thanks a lot. These are great examples. A few minutes ago I finally figured
out I should be searching for "crosstab".

Rick B said:
and one more...


Thanks.

It works.

Michel Walsh said:
Hi,


Definitively, you then fall back on more standard ground. You first rank
each values ( 380 is first, 609 is 2nd and 610 is 3rd), and then, pivot on
that rank.


To rank data, a possible technique is to use a GROUP BY on a join:


----------------------------------------------------
SELECT a.value, COUNT(*) As rank

FROM myTable As a INNER JOIN myTable as b
ON a.value <= b.value

GROUP BY a.value
---------------------------------------------------


save it as, let say, q1. The crosstab is then


-------------------------------------------------
TRANSFORM Last(value)
SELECT null
FROM q1
GROUP BY 1
PIVOT rank
------------------------------------------------


Hoping it may help,
Vanderghast, Access MVP





MK said:
Love this query !

But ,

If the data is 380
609
611

The results are Coulmns named Expr1, 380,609, 611
Containing null, 380,609,611

Is there anyway to standardize the column names regardless of the data
i.e. Columns named Expr1, COL1, COL2, COL3
Conataining null, 380,609,611

Thanks,

Mike

:

Hi,


Sounds like a very special crosstab case:

PARAMETERS Country Text ( 255 );
TRANSFORM LAST(number)
SELECT null
FROM ( SELECT Count(1SEs.SEAccNumber) AS Number
FROM 1SEs
WHERE 1SEs.Country=Country
GROUP BY 1SEs.SignMonth ) As x
GROUP BY 1
PIVOT number




If you have to open a recordset on your actual query, another solution is
to
use GetRows() on the recordset.... GetRows( ) transpose the data in a
tableau of variants.


Hoping it may help,
Vanderghast, Access MVP

message
Hi,

I am trying to change queries that produce one vertical column (field)
of
results and many rows (records) into one that is transposed to be one
horizontal row. Is there any simple way / function to do this?

E.g. my parameter query below.

PARAMETERS Country Text ( 255 );
SELECT Count([1SEs].[SEAccNumber]) AS [Number]
FROM 1SEs
WHERE ((([1SEs].Country)=[Country]))
GROUP BY [1SEs].SignMonth;

.produces.

1150
1200
1644
1429

but I need to produce

1150 1200 1644 1429

so that I can use this sort of query and VBA to CopyfromRecordset
straight
into Excel spreadsheet rows.

Can this be done?

Many thanks in advance.

Robert
Click to expand...
Click to expand...
hogwart said:
Sorry for lookiing stupid. I searched for case statements, but none answered
exactly what I needed. What should I search for exactly?
Click to expand...
Click to expand...
 
My source is:

123 table
123 chair
123 dish
234 table
234 bowl
345 table
345 spoon
345 dish

My expected result:

123 table chair disk
234 table bowl
345 table spoon dish

Could anyone tell me how I can get the expected result by running a query in
Access? The crosstab does not fit this as I need the column values to align
altogether from left to the right with no empty values shown.

Thanks in advance.

chu888



wart said:
Thanks a lot. These are great examples. A few minutes ago I finally figured
out I should be searching for "crosstab".

Rick B said:
and one more...


Thanks.

It works.

Michel Walsh said:
Hi,


Definitively, you then fall back on more standard ground. You first rank
each values ( 380 is first, 609 is 2nd and 610 is 3rd), and then, pivot on
that rank.


To rank data, a possible technique is to use a GROUP BY on a join:


----------------------------------------------------
SELECT a.value, COUNT(*) As rank

FROM myTable As a INNER JOIN myTable as b
ON a.value <= b.value

GROUP BY a.value
---------------------------------------------------


save it as, let say, q1. The crosstab is then


-------------------------------------------------
TRANSFORM Last(value)
SELECT null
FROM q1
GROUP BY 1
PIVOT rank
------------------------------------------------


Hoping it may help,
Vanderghast, Access MVP





Love this query !

But ,

If the data is 380
609
611

The results are Coulmns named Expr1, 380,609, 611
Containing null, 380,609,611

Is there anyway to standardize the column names regardless of the data
i.e. Columns named Expr1, COL1, COL2, COL3
Conataining null, 380,609,611

Thanks,

Mike

:

Hi,


Sounds like a very special crosstab case:

PARAMETERS Country Text ( 255 );
TRANSFORM LAST(number)
SELECT null
FROM ( SELECT Count(1SEs.SEAccNumber) AS Number
FROM 1SEs
WHERE 1SEs.Country=Country
GROUP BY 1SEs.SignMonth ) As x
GROUP BY 1
PIVOT number




If you have to open a recordset on your actual query, another solution is
to
use GetRows() on the recordset.... GetRows( ) transpose the data in a
tableau of variants.


Hoping it may help,
Vanderghast, Access MVP

message
Hi,

I am trying to change queries that produce one vertical column (field)
of
results and many rows (records) into one that is transposed to be one
horizontal row. Is there any simple way / function to do this?

E.g. my parameter query below.

PARAMETERS Country Text ( 255 );
SELECT Count([1SEs].[SEAccNumber]) AS [Number]
FROM 1SEs
WHERE ((([1SEs].Country)=[Country]))
GROUP BY [1SEs].SignMonth;

.produces.

1150
1200
1644
1429

but I need to produce

1150 1200 1644 1429

so that I can use this sort of query and VBA to CopyfromRecordset
straight
into Excel spreadsheet rows.

Can this be done?

Many thanks in advance.

Robert
Click to expand...
hogwart said:
Sorry for lookiing stupid. I searched for case statements, but none answered
exactly what I needed. What should I search for exactly?

:

I've seen almost identical posts in this newsgroup many many many times in
the last few weeks. Did you try searching for your answer first?

Rick B


I need an Access query to convert data from this format:

key flag
user1 A
user1 B
user1 C

to this format:

key flag
user1 A,B,C

I can do it crudely in sql (for sql server), but it takes several temp
table
steps and uses the case statement.

Thanks for any help.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your sample code.
I shall try and see how it work.

Actually my expected result should look like this:

id product product product
123 table chair dish
234 table bowl
345 table spoon dish

whereas id ... is column heading and not all id has equal numbers of products.

Is it possible in your sample query to create this column headings even they
have various number of products in each row.

Chu888
 
You can't derive columns wiht the same name. You could create a crosstab
that uses a column heading expression that concatenates "Product" with
1,2,3,4,...
 
Duane,

The reason why I need to get the expected result is because I shall use this
format to run a mail merge in Word. So I need a separate column heading with
single value, not concatenated it with multiple values separated by a comma.
Is there any way to do it?

Chu888
 
I didn't see any column headings in your sample. You can use the concatenate
function and include the tab character as the delimiter to create columns.
 
Back
Top