Count the number of times the name

I have an access database setup as follows:

Field1 Field2 Field3 Field4
John Mike Mike Larry
Steve Danny Mike John


Using vb what is the query to display the data like this:

Position Name How many

1 Mike 3
2 John 2
3 Larry 1
4 Steve 1
5 Danny 1

In the top thread you have an FAQ on SQL statements use the Count function.

E.g:

SELECT Count(Field1) FROM table1 Where field1 = 'mike'

Would be a simple way. Now using the distinct function in there would eliminate the need for the WHere statement.

E.g:

SELECT DISTINCT Count(field1) FROM table1

good luck
b :)

You've a cruddy table design & really should fix it before you proceed, but this should help you on your way...

Code:

---

-- TSQL
SELECT fName, COUNT(fName) AS HowMany FROM
( SELECT Field1 FROM FoolishTable
  UNION ALL
  SELECT Field2 FROM FoolishTable
  UNION ALL
  SELECT Field3 FROM FoolishTable
  UNION ALL
  SELECT Field4 FROM FoolishTable ) AS NeedDBA(fName)
GROUP BY fName
ORDER BY COUNT(fName) DESC

-- MS Access
SELECT Field1 AS fName, Count(Field1) AS HowMany FROM
( SELECT Field1, 1 FROM FoolishTable
  UNION ALL
  SELECT Field2, 2 FROM FoolishTable
  UNION ALL
  SELECT Field3, 3 FROM FoolishTable
  UNION ALL
  SELECT Field4, 4 FROM FoolishTable )
GROUP BY Field1
ORDER BY COUNT(Field1) DESC;

---