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
