If you want to display them in order then you could use one of two methods.
If they don't need to be in separate fields, you could use Duane
Hookom's concatenate function.
Quoting Duane Hookom
I use a generic Concatenate() function. The code is listed below with
both ADO and DAO. There are comments regarding which lines to comment or
uncomment based on which library you prefer. Access 97 is mostly DAO
while the default for 2000 and newer is ADO.
See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
Otherwise you could use a ranking query to assign value to the rndVal
fields and then use that as the source of a cross tabl query
QRank:
SELECT A.[SrNo], A.RndVal, Count(B.RndVal) as Rank
FROM YourTable as A LEFT JOIN YourTable as B
ON A.[SRNo] = B.[SrNo]
AND A.rndVal > B.RndVal
GROUP BY A.[SrNo], A.RndVal
Now use that query as the source for a crosstab query that would look
something like the following. You will need to enter the one RndVal#
entry in the "In" clause for each (maximum number) of rndVal columns
that will be returned.
TRANSFORM First(Q.RndVal) as TheValue
SELECT Q.[SrNo]
FROM QRank as Q
GROUP BY Q.[SrNo]
Pivot "RndVal" & Rank + 1 in
("RndVal1","RndVal2","RndVal3","RndVal4","RndVal5","RndVal6")
Now you should be able to build a report based on the above query.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
