Some kind of transposing

G

Gunti

Hi,

I'm creating a query with the following

940000 F716
940000 F720
940000 F730
940001 A130
940001 A134
940001 A135

What i'm trying to get out of it is:

940000 F716 F20 F30
940001 A130 A134 A135

etc.

Basisicly the if value in column 1 is the same it needs to put the value's
behind it.

Is this possible in Access with a query??

gr,
Gunti
 
J

John Spencer

If the requirement is to put the values into separate columns then you
can use a ranking query and a crosstab to get the results.

If the requirement is to put the additional data in one column that is
separated by a delimiter (spaces, semi-colons, slashes, etc) then you
need to use a VBA function to combine items.

Assuming the first
SELECT A.FirstColumn, A.SecondColumn
, Count(B.FirstColumn) as ColumnPosition
FROM [Your Table] as A LEFT JOIN [Your Table] as B
ON A.FirstColumn = B.FirstColumn
AND A.SecondColumn > B.SecondColumn
GROUP BY A.FirstColumn

Now use that query as the source for a cross-tab query

TRANSFORM First(SecondColumn)
SELECT FirstColumn
FROM queryAbove
GROUP BY FirstColumn
PIVOT ColumnPosition


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top