Query

[Guest]

I have a table that has 20 fileds. Within the 20 fields, 14 of them may or
may not have a number greater then zero. I would like to count the numbers
in those fields.

Any ideas?

TFTH,
Tom

 

[Guest]

Hi Tom,

When you say that you want to count the fields, do you want to count how
many records in each field include a value of 0 or greater, or do you want to
sum the data? For example, using this field:

Field1
2
0
-4
5

The count of records that are zero or greater is 3.
The sum of records that are zero or greater is 7.

In any case, you can accomplish either goal using a query or a report.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Tom,
Thanks for the response, However I think that stated my quest wrong. I need
to know in a single record how may of the 14 fields are greater than 0.

 

[Guest]

Okay, so you want something like this:

Field1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field9
1 0 -2 4 -1 6
-5 4


Count of fields in this record with a value of zero or greater = 5
Is this right? If your answer is Yes, then you could use the following query:

SELECT (IIf([Field1]>=0,1,0))+(IIf([Field2]>=0,1,0))
+(IIf([Field3]>=0,1,0))+(IIf([Field4]>=0,1,0))
+(IIf([Field5]>=0,1,0))+(IIf([Field6]>=0,1,0))
+(IIf([Field7]>=0,1,0))+(IIf([Field8]>=0,1,0))
+(IIf([Field9]>=0,1,0))+(IIf([Field10]>=0,1,0))
+(IIf([Field11]>=0,1,0))+(IIf([Field12]>=0,1,0))
+(IIf([Field13]>=0,1,0))+(IIf([Field14]>=0,1,0))

AS [Number Fields]
FROM TableName;

It's a little ugly, but not too bad. Basically, we are using a conditional
IF statement (IIF) to test whether the field is greater than or equal to
zero. If it is, return a 1 otherwise return a zero. We then add the results
together. If a particular field is null, then the IIF([FieldName]>=0 will
evaluate to null.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________