Counting Across Fields

  • Thread starter Thread starter Mike DeNuccio
  • Start date Start date
M

Mike DeNuccio

Is it possible in a query to count the number of times a certain item
appears in multiple fields? For example, I have four fields

Field Item in Field
1 A
2 B
3 (null)
4 B

Is it possible to count the number of times A appears so that in Field
ACount I would have 2. In Field BCount, I would have 1. In the end, I
would like to be able to divide by the number of fields I am searching
through, so that the values in ACount and BCount would be .5 ad .25
respectively, but I figured I could figure that part out if someone could
give me the first part.

As I am not great with VB, I would prefer a query solution, but will take
anything. It's easy in Excel, but Access seems to make it hard to do this.

Thanks in advance.

Mike
 
I tried one solution on my own and got pretty close, but it doesn't work.
What I tried was to use the length of a combination of the fields, but this
worked only if there were two options (A and NULL).

Len([1]&[2]&[3]&[4])/4

returned what I need, except that I need it to differentiate the A and B
values.

I tried searching for ways to count the occurence of a letter in a string,
but could not find anything.

Thanks for help.
 
Dear Mike:

First, normalize this so it has more rows, but only one field. A
UNION query is the way to do this:

SELECT Field1 AS Item FROM YourTable WHERE Field1 IS NOT NULL
UNION ALL
SELECT Field2 AS Item FROM YourTable WHERE Field2 IS NOT NULL
UNION ALL
SELECT Field3 AS Item FROM YourTable WHERE Field3 IS NOT NULL
UNION ALL
SELECT Field4 AS Item FROM YourTable WHERE Field4 IS NOT NULL

From this, saved as UnionQuery

SELECT Item, COUNT(*) FROM UnionQuery GROUP BY Item

It would be much better if your tables are structured already
normalized. The information is much more readily accessible this way.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top