display odd\even numbers

[stenman]

Would like to create a count for counting odd and even numbers in a field. Let say I have field1 and field2 declared as number and here I have 100 numbers in each row. How to create a sql in access counting how many odd and even numbers are represented in each field, the best would have two new fields(one for odd and one for even per row)?



The sql should be a "inner-sql" since I already have the "outer-sql" doing something else!

[JoeM]

Let say I have field1 and field2 declared as number and here I have 100 numbers in each row.

Not quite clear what you mean by this. Do you mean you have 100 numbers entered in a single field (i.e. field1)? If so, how are they separated?
Or do you have 100 different records, each with two field values, field1 and field2?

[stenman]

exactly, I have 100 different records with two field values, field1 and field2...

[JoeM]

You could write an IIF statement using the MOD function to check for evens/odds, returning a 1 or zero, and then SUM those calculations in an Aggregate Query to get the counts.
Something like this:

SELECT 
    Sum(IIf([Field1] Mod 2=0,1,0)) AS Field1_Evens, 
    Sum(IIf([Field1] Mod 2=1,1,0)) AS Field1_Odds, 
    Sum(IIf([Field2] Mod 2=0,1,0)) AS Field2_Evens, 
    Sum(IIf([Field2] Mod 2=1,1,0)) AS Field2_Odds
FROM 
    MyTable;