Group Column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that list three different items for each row so it ends up
displaying the row three times in order to see all three items in the one
column. Can anyone send me some example code so I can group the three
different items so I only have one row?

Thank you in advance,
Tony
 
Hi,


Rank the records within the group, then, run a crosstab query, using the
rank as PIVOT.


MyTable
Item, Color ' fields name
1, blue
1, red
1, green
2, green
2, blue,
2, yellow
2, black ' data



SELECT a.item, a.color, COUNT(*) As rank
FROM myTable As a INNER JOIN myTable As b
ON a.item=b.item AND a.color >= b.color
GROUP BY a.item, a.color


will rank. Save that query under the name, for illustration, q1


TRANSFORM LAST(color)
SELECT item
FROM q1
GROUP BY item
PIVOT rank



supply a result as required.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top