3 Fields, 1 Total

[athyeh]

Hi Folks,

Got a new question for today:



I have a table with 4 fields:

Date
Operator1
Operator2
Operator3

How do I create a query that counts all the recods where "Johnny" shows up in Operator1, Operator2, or Operator3?

Thanks,

Tony

[JoeM]

A perfect example of how unnormalized data can make simple tasks harder than they have to be!

A well-designed table should be structured like this, with only three fields.

Date
Operator Number
Operator Name

So then, the task is pretty easy, as you can create a Totals Query and Group By the Operator Name field and use the Count function on one of the other fields to get your account.

As you currently have it structured, there are a few different ways to do it. Here are a few:

Count each of the three Operator columns separately (via queries), and then add them
- or -
Create a query with a calculated field that will return a 1 if any of the three fields have that name in there, and a 0 otherwise.
Then you can Perform a Totals Query and use the SUM function on this calculated field to get your count.

[Dal Jeanis]

SELECT Count(MyTable.MyDate)
FROM MyTable
WHERE ((Operator1 = "Johnny")  
   OR  (Operator2 = "Johnny")  
   OR  (Operator3 = "Johnny"));

or, if you want to count anything that starts with John, use

SELECT Count(MyTable.MyDate)
FROM MyTable
WHERE ((Operator1 Like "John*")  
   OR  (Operator2 Like "John*")  
   OR  (Operator3 Like "John*")) ;

[athyeh]

You're the man Dal. Thanks!