Counting from Multiple Columns

[RSteph]

I've got a table with 5 columns and roughly 75 rows. In each of these fields
is a random number from 1-20. I'd like to get a count of how many of each
number exists, but within a single column; and overall through the 5 columns.

Does anyone know a good way to create a query that shows the numbers (1-20)
and then a count for each column and a "total count" column?

Any help would be greatly appreciated.

 

[KARL DEWEY]

You have a spreadsheet instead of a relational database.
Use a union query like this --
PullTogether --
SELECT 1 AS Column, Column1 AS Value
From YourTable
UNION ALL SELECT 2 AS Column, Column2 AS Value
From YourTable
UNION ALL SELECT 3 AS Column, Column3 AS Value
From YourTable
UNION ALL SELECT 4 AS Column, Column4 AS Value
From YourTable
UNION ALL SELECT 5 AS Column, Column5 AS Value
From YourTable;

SELECT Value, Count([Value]) AS CountOfValue
FROM PullTogether
GROUP BY Value;

and --
SELECT Column, Value, Count([Value]) AS CountOfValue
FROM PullTogether
GROUP BY Column, Value;