Count occurances of name

[green'n'white]

Hi,

using asp I have an access table with a number of colums ie, col1, col2, col3, which contain a number of randomly repeated names in each of the rows.

What i would like to do is return the number of times each name is present in all of the columns and present this in a table on my site,

i.e.

john - 2
dave - 3
etc

any help will be greatly appreciated,

[chazzy]

select count(col1), col1 FROM your_table GROUP BY col1;

you would have to do that individually for each column though. unless access does views which i doub.t

[aussie girl]

This works in MySQL, not sure about MS DB's

SELECT count(col1), count(col2), count(col3) FROM `tablename`
WHERE name = ' A name'

[chazzy]

that should work, but i think he's saying the name value is in col1,col2 or col3.