Conversion of Rows into Colums

[BusyProfessorFromBremen]

Hi @ all,

I am experiencing a problem by working onMS Access. I have got a huge table
containing a lot of information (both columns and rows). There are rows
listed with repeating values containing the same ID Number. To this ID number
there are at maximun 3 other values related.
Thus I would like to put these 3 values into one row, that one row belongs
to one particular IDnumber.

EXAMPLE:

State of the art

ID VALUEs

123 5
123 6
123 7

The result should be:

ID Value 1 Value 2 Value 3
123 5 6 7

It would be very kind if anybody has got a clou how to handle this affair.

Kind regards and thanks in advance

malte

 

[John Spencer MVP]

One way would be to Rank the Values in a query and then use a crosstab query
for the display.

This query should return the ID, the value, and a rank from 0 to 2 ( or
possibly more if you have more than three values).

SELECT T1.ID, T1.[Value],
Count([T2].[Value]) as Rank
FROM TheTable As T1 LEFT JOIN TheTable As T2
ON T1.ID = T2.ID AND T1.[Value] < T2.[Value]
GROUP BY T1.ID, T1.[Value]

This crosstab query would use the above as its source to produce the result
you are looking for

TRANSFORM First([value]) as TheValue
SELECT ID
FROM TheAboveSavedQuery
GROUP BY ID
PIVOT "Value " & Rank

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

 

[BusyProfessorFromBremen]

Dear John,

thank you very much for your fast and professional response!

As I had a look over your sql statement I realised a problem by implementing
it. The ID can occur once, twice, even six timeand there is no rule
identifyable which can clarify an order of these numbers. As I spoke to some
friends which are very capable of programming VBA they adviced me to do it
with an inner and outer loop. First I need to seperate the doubled, trippled
etc. numbers into a group and further more pick the information out of the
bunch of number which are differing to each other. Thus the outcome should be
on row and different columns replacing the former doubled, trippled etc. rows.

An example to visualize the problem again:

ID Presstype

123 22op
123 23oo
123 01gq
294 10hd
294 18fq

ID Option1 Option2 Option3
123 22op 23oo 01gq
294 10hd 18fq

If you transfer your knwoledge into a similar clever vba solution you would
help me out by far. Thanks in advance.

Yours sincerely

Malte

One way would be to Rank the Values in a query and then use a crosstab query
for the display.

This query should return the ID, the value, and a rank from 0 to 2 ( or
possibly more if you have more than three values).

SELECT T1.ID, T1.[Value],
Count([T2].[Value]) as Rank
FROM TheTable As T1 LEFT JOIN TheTable As T2
ON T1.ID = T2.ID AND T1.[Value] < T2.[Value]
GROUP BY T1.ID, T1.[Value]

This crosstab query would use the above as its source to produce the result
you are looking for

TRANSFORM First([value]) as TheValue
SELECT ID
FROM TheAboveSavedQuery
GROUP BY ID
PIVOT "Value " & Rank

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

Hi @ all,

I am experiencing a problem by working onMS Access. I have got a huge table
containing a lot of information (both columns and rows). There are rows
listed with repeating values containing the same ID Number. To this ID number
there are at maximun 3 other values related.
Thus I would like to put these 3 values into one row, that one row belongs
to one particular IDnumber.

EXAMPLE:

State of the art

ID VALUEs

123 5
123 6
123 7

The result should be:

ID Value 1 Value 2 Value 3
123 5 6 7

It would be very kind if anybody has got a clou how to handle this affair.

Kind regards and thanks in advance

malte

 

[John Spencer]

Unless I am misunderstanding, the solution I proposed will do what you
want except in the case of where you have pairs of duplicated data. In
those cases the result will be slightly different from what you want and
you will get only one of the matching pairs reported. Note that the
first pair is repeated.

ID Presstype
123 22op
123 22op
123 23op
123 23op
123 01gq
294 10hd
294 18fq

The first query should return numbers like the following IF you had
duplicate pairs. If you don't have duplicate pairs, then the numbers
will be sequential with no breaks.

ID Presstype Rank
123 22op 1
123 22op 1
123 23op 3
123 23op 3
123 01gq 0

294 10hd 0
294 18fq 1

Try the suggestion and if it fails post back and tell us what the
problem with the results is.

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

Dear John,

thank you very much for your fast and professional response!

As I had a look over your sql statement I realised a problem by implementing
it. The ID can occur once, twice, even six timeand there is no rule
identifyable which can clarify an order of these numbers. As I spoke to some
friends which are very capable of programming VBA they adviced me to do it
with an inner and outer loop. First I need to seperate the doubled, trippled
etc. numbers into a group and further more pick the information out of the
bunch of number which are differing to each other. Thus the outcome should be
on row and different columns replacing the former doubled, trippled etc. rows.

An example to visualize the problem again:

ID Presstype

123 22op
123 23oo
123 01gq
294 10hd
294 18fq

ID Option1 Option2 Option3
123 22op 23oo 01gq
294 10hd 18fq

If you transfer your knwoledge into a similar clever vba solution you would
help me out by far. Thanks in advance.

Yours sincerely

Malte

One way would be to Rank the Values in a query and then use a crosstab query
for the display.

This query should return the ID, the value, and a rank from 0 to 2 ( or
possibly more if you have more than three values).

SELECT T1.ID, T1.[Value],
Count([T2].[Value]) as Rank
FROM TheTable As T1 LEFT JOIN TheTable As T2
ON T1.ID = T2.ID AND T1.[Value] < T2.[Value]
GROUP BY T1.ID, T1.[Value]

This crosstab query would use the above as its source to produce the result
you are looking for

TRANSFORM First([value]) as TheValue
SELECT ID
FROM TheAboveSavedQuery
GROUP BY ID
PIVOT "Value " & Rank

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

Hi @ all,

I am experiencing a problem by working onMS Access. I have got a huge table
containing a lot of information (both columns and rows). There are rows
listed with repeating values containing the same ID Number. To this ID number
there are at maximun 3 other values related.
Thus I would like to put these 3 values into one row, that one row belongs
to one particular IDnumber.

EXAMPLE:

State of the art

ID VALUEs

123 5
123 6
123 7

The result should be:

ID Value 1 Value 2 Value 3
123 5 6 7

It would be very kind if anybody has got a clou how to handle this affair.

Kind regards and thanks in advance

malte

 

[BusyProfessorFromBremen]

Dear John,

as I would like to convert every single information whether it is doubled or
trippled I need to define unique ranks for every single rows withín recordset
(defined by the id).

There is no option to do so with a sql query. Thus I implemented an unique
and automacially generated id attached to the intial table.

In the following I will count the records within the recordset related to
the id. The condition for the ranking is dependant on the id. Hence the
outcome is a recordset which is seperated into continous ranks.

The Result:

ID The Value IDNEW RANK
123 2 1 1
123 2 2 2
..
..
..
254 3 25342 1
254 4 25343 2

That's satisfying so far, but i experience problem by converting it through
a transformation into PIVOT table. The programm can't identify the ID
anymore....If you got an creative idea to handle this case I would be very
glad to know.

Thank you so much for so far!

malte

Unless I am misunderstanding, the solution I proposed will do what you
want except in the case of where you have pairs of duplicated data. In
those cases the result will be slightly different from what you want and
you will get only one of the matching pairs reported. Note that the
first pair is repeated.

ID Presstype
123 22op
123 22op
123 23op
123 23op
123 01gq
294 10hd
294 18fq

The first query should return numbers like the following IF you had
duplicate pairs. If you don't have duplicate pairs, then the numbers
will be sequential with no breaks.

ID Presstype Rank
123 22op 1
123 22op 1
123 23op 3
123 23op 3
123 01gq 0

294 10hd 0
294 18fq 1

Try the suggestion and if it fails post back and tell us what the
problem with the results is.

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

Dear John,

thank you very much for your fast and professional response!

As I had a look over your sql statement I realised a problem by implementing
it. The ID can occur once, twice, even six timeand there is no rule
identifyable which can clarify an order of these numbers. As I spoke to some
friends which are very capable of programming VBA they adviced me to do it
with an inner and outer loop. First I need to seperate the doubled, trippled
etc. numbers into a group and further more pick the information out of the
bunch of number which are differing to each other. Thus the outcome should be
on row and different columns replacing the former doubled, trippled etc. rows.

An example to visualize the problem again:

ID Presstype

123 22op
123 23oo
123 01gq
294 10hd
294 18fq

ID Option1 Option2 Option3
123 22op 23oo 01gq
294 10hd 18fq

If you transfer your knwoledge into a similar clever vba solution you would
help me out by far. Thanks in advance.

Yours sincerely

Malte

One way would be to Rank the Values in a query and then use a crosstab query
for the display.

This query should return the ID, the value, and a rank from 0 to 2 ( or
possibly more if you have more than three values).

SELECT T1.ID, T1.[Value],
Count([T2].[Value]) as Rank
FROM TheTable As T1 LEFT JOIN TheTable As T2
ON T1.ID = T2.ID AND T1.[Value] < T2.[Value]
GROUP BY T1.ID, T1.[Value]

This crosstab query would use the above as its source to produce the result
you are looking for

TRANSFORM First([value]) as TheValue
SELECT ID
FROM TheAboveSavedQuery
GROUP BY ID
PIVOT "Value " & Rank

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

BusyProfessorFromBremen wrote:
Hi @ all,

I am experiencing a problem by working onMS Access. I have got a huge table
containing a lot of information (both columns and rows). There are rows
listed with repeating values containing the same ID Number. To this ID number
there are at maximun 3 other values related.
Thus I would like to put these 3 values into one row, that one row belongs
to one particular IDnumber.

EXAMPLE:

State of the art

ID VALUEs

123 5
123 6
123 7

The result should be:

ID Value 1 Value 2 Value 3
123 5 6 7

It would be very kind if anybody has got a clou how to handle this affair.

Kind regards and thanks in advance

malte

 

[John Spencer]

I understand from your post that IDNew is a continuous unique sequential
value. If you have the data as you shown in the result, then you should
be able to construct a pivot (crosstab ) query.

TRANSFORM First([The Value]) as X
SELECT ID
FROM [The Result]
GROUP BY ID
PIVOT Rank

If you are saying you need an SQL statement to generate the rank based
on ID and IDNew then the following should work

SELECT A.ID, A.PressType, 1+Count(B.ID) as Rank
FROM YourTable as A LEFT JOIN YourTable As B
ON A.ID = B.ID
AND A.IDNew > B.IDNew
GROUP BY A.ID, A.PressType


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

Dear John,

as I would like to convert every single information whether it is doubled or
trippled I need to define unique ranks for every single rows withín recordset
(defined by the id).

There is no option to do so with a sql query. Thus I implemented an unique
and automacially generated id attached to the intial table.

In the following I will count the records within the recordset related to
the id. The condition for the ranking is dependant on the id. Hence the
outcome is a recordset which is seperated into continous ranks.

The Result:

ID The Value IDNEW RANK
123 2 1 1
123 2 2 2
.
.
.
254 3 25342 1
254 4 25343 2

That's satisfying so far, but i experience problem by converting it through
a transformation into PIVOT table. The programm can't identify the ID
anymore....If you got an creative idea to handle this case I would be very
glad to know.

Thank you so much for so far!

malte

Unless I am misunderstanding, the solution I proposed will do what you
want except in the case of where you have pairs of duplicated data. In
those cases the result will be slightly different from what you want and
you will get only one of the matching pairs reported. Note that the
first pair is repeated.

ID Presstype
123 22op
123 22op
123 23op
123 23op
123 01gq
294 10hd
294 18fq

The first query should return numbers like the following IF you had
duplicate pairs. If you don't have duplicate pairs, then the numbers
will be sequential with no breaks.

ID Presstype Rank
123 22op 1
123 22op 1
123 23op 3
123 23op 3
123 01gq 0

294 10hd 0
294 18fq 1

Try the suggestion and if it fails post back and tell us what the
problem with the results is.

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

Dear John,

thank you very much for your fast and professional response!

As I had a look over your sql statement I realised a problem by implementing
it. The ID can occur once, twice, even six timeand there is no rule
identifyable which can clarify an order of these numbers. As I spoke to some
friends which are very capable of programming VBA they adviced me to do it
with an inner and outer loop. First I need to seperate the doubled, trippled
etc. numbers into a group and further more pick the information out of the
bunch of number which are differing to each other. Thus the outcome should be
on row and different columns replacing the former doubled, trippled etc. rows.

An example to visualize the problem again:

ID Presstype

123 22op
123 23oo
123 01gq
294 10hd
294 18fq

ID Option1 Option2 Option3
123 22op 23oo 01gq
294 10hd 18fq

If you transfer your knwoledge into a similar clever vba solution you would
help me out by far. Thanks in advance.

Yours sincerely

Malte


:

One way would be to Rank the Values in a query and then use a crosstab query
for the display.

This query should return the ID, the value, and a rank from 0 to 2 ( or
possibly more if you have more than three values).

SELECT T1.ID, T1.[Value],
Count([T2].[Value]) as Rank
FROM TheTable As T1 LEFT JOIN TheTable As T2
ON T1.ID = T2.ID AND T1.[Value] < T2.[Value]
GROUP BY T1.ID, T1.[Value]

This crosstab query would use the above as its source to produce the result
you are looking for

TRANSFORM First([value]) as TheValue
SELECT ID
FROM TheAboveSavedQuery
GROUP BY ID
PIVOT "Value " & Rank

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

BusyProfessorFromBremen wrote:
Hi @ all,

I am experiencing a problem by working onMS Access. I have got a huge table
containing a lot of information (both columns and rows). There are rows
listed with repeating values containing the same ID Number. To this ID number
there are at maximun 3 other values related.
Thus I would like to put these 3 values into one row, that one row belongs
to one particular IDnumber.

EXAMPLE:

State of the art

ID VALUEs

123 5
123 6
123 7

The result should be:

ID Value 1 Value 2 Value 3
123 5 6 7

It would be very kind if anybody has got a clou how to handle this affair.

Kind regards and thanks in advance

malte