Count Fields (1 Viewer)

[stonemason]

For Info
Record 1 Field1=2 Field2=4 Field3=8

Record 2 Field1=2 Field2=4 Field3=8

Record 3 Field1=2 Field2=4 Field3=8

If say the criteria is =>4
How can I count the number of individual fields that match this criteria Not count the records or total the fields but just count the total number of fields in all records which match this criteria.

any help would be appreciated

stonemason

 

[pcs]

if i read your question correctly...

check in Help for:

Partition Function

hth,
al

 

[Pat Hartman]

Sounds like a table design issue. Fields 1,2, and 3 should most likely be rows in a separate table rather than columns in this one.

To obtain the result you want without restructuring the table will require 5 queries. One each to count the number of rows matching the criteria for each of the three columns, the fourth query to union the results of the first three, and the fifth, to sum the recordset produced by the union. All in all, proper table design solves many analysis problems.