nonuser looking for simple count

[Amy]

I have data that looks like this:

Column Name: L1 L2 L3
Values: X Y Z
A X S
Y S Z
B Y A

I need to create a report, table, or query that can list
each value that shows up in L1, L2, or L3 and count the
number of times each value shows up in total. A solution
would look something like:

Value Count
A 2
B 1
S 2
X 2
Y 3
Z 2

I can do this in Excel, SAS, or Fortran, but I don't use
Access enough to understand how to even search for a
solution. I need to use Access because someone else will
be changing the values, etc. I've tried a few
possibilities that didn't work. Is there an easy way to
do this?

 

[John Spencer (MVP)]

Well, you should change the table structure, but if you can't do that you will
need to use a "normalizing" union query and then running a totals query against that.

SELECT L1
FROM TableName
UNION ALL
SELECT L2
FROM TableName
UNION ALL
SELECT L3
FROM TableName

Save that as qryNormal and then

SELECT L1, Count(L1) as RecCount
FROM qryNormal
GROUP BY L1

You MIGHT be able to do this as one query, but I don't have time to try testing this

SELECT Tbl.L1, Count(tbl.L1) as RecCount
FROM (SELECT L1
FROM TableName
UNION ALL
SELECT L2
FROM TableName
UNION ALL
SELECT L3) as Tbl
GROUP BY tbl.L1

 

[John Vinson]

I have data that looks like this:

Column Name: L1 L2 L3
Values: X Y Z
A X S
Y S Z
B Y A

I need to create a report, table, or query that can list
each value that shows up in L1, L2, or L3 and count the
number of times each value shows up in total. A solution
would look something like:

Well... part of the problem is that your table is incorrectly
structured. Having three fields all sharing the "same" data is
explicitly bad design! This can be done but it's more complicated by
far than it would be if you had a one-to-many relationship to a table
with one ROW per value, rather than one FIELD per value.

To do this you'll need a Query based on another Query. First create a
Query in the SQL window by typing in:

SELECT L1 AS Value From YourTableName
UNION ALL
SELECT L2 From YourTableName
UNION ALL
SELECT L3 From YourTableName;

Save this query as uniAllValues.

Then create a second query based on uniAllValues; add the Value field
to the query TWICE. Change the query to a Totals query by clicking the
Greek Sigma icon. Leave the first "Group By" as it is on the Totals
line, and change it to Count on the second.