Count names issue

[Flemming]

Hi

Thank you for using time on my issue.

I have created a query "quyRoundPrices" that give me the following result
Year Round FirstP SecondP Long One Two
2006 1 Peter Will Will Peter
Dennis
2006 2 Will Charles Dennis Peter Will
this goes on for 6 rounds


Now I would like to create an query based on "quyRoundPrices" that returns
the number of prices that a person have earned in a year.

Result for 2006
Name Prices
Will 4
Peter 3
Dennis 2
Charles 1


My eyes have gone 'blind' and I can not se how the sql sentence should look
like.

Thanks alot
Flemming

 

[Michel Walsh]

You have to normalize your table, ie, to have the data vertically, not
horizontally, as it is now.


SELECT year, FirstP AS person FROM myTable
UNION ALL
SELECT year, SecondP FROM myTable
UNION ALL
SELECT year, Long FROM myTable
UNION ALL
SELECT year, One FROM myTable
UNION ALL
SELECT year, Two FROM myTable



save it as a query, say q1. Then



SELECT person, COUNT(*) FROM q1 GROUP BY person


simply does the job (simply, now that it is normalized).


Hoping it may help,
Vanderghast, Access MVP

 

[Flemming]

Hi Michel

Thanks alot - that was JUST what I needed

Thanks
Flemming