Not easy to read, but it works !
First query (called 'Voorwerk'):
SELECT People.Naam, People.Age, DCount("[name]","People","[Age]>" & [Age] &
" AND [name]='" & [name] & "'")+1 AS Expr1
FROM People;
Second query (based on the first)
TRANSFORM First(Voorwerk.Age) AS FirstOfAge
SELECT Voorwerk.Name, Count(Voorwerk.Name) AS [Number]
FROM Voorwerk
GROUP BY Voorwerk.Name
PIVOT Voorwerk.Expr1;
Input table (People)
Name Age
John 45
John 23
John 10
Peter 78
Peter 25
Result of the query is
Name Number 1 2 3
John 3 45 23 10
Peter 2 78 25
The trick is in the DCount function. This counts the number of occurrences
in the table where the name is equal to the name of that row, and the age is
less than the age of that current row. In effect, it creates an row number
for any given name when you would order the available data by age
(descending). That row number is used to create column headers in the
pivot-table (=cross-tab query).
I would not recommend installing this at some client's site though, because
it is nearly impossible to read what's happening here. I -for one- cannot
see a practical use for it, so this is just to prove the case that anything
is possible.
Bas Hartkamp.
<ja********@gmail.com> schreef in bericht
news:11*********************@i40g2000cwc.googlegro ups.com...
Hi,
is it possible to do something like this:
-------------------------
name | age
-------------------------
john | 45
john | 23
john | 10
peter | 78
peter | 25
query to this:
---------------------------------------
name | age | number
---------------------------------------
john | 45, 23, 10 | 3
peter | 78, 25 | 2
????
thANKS