FUNCTION PREDICTING EVEN/ODD COUNT IN QUERY

[Angela]

Hey,

I have set of queries in access which determine the following:

Query1- Group single occurance together.
Query2- Group all 2, 4, 6, 8, 10, 12 occurances together. Even occurance.
Query3- Group all 3, 5, 7, 9, 11, 13 occurances together. Odd occurance.

Union of Query1 and Query3 gives me the desired table.
Query2 is ignored, since even pairs in which one or a set of 2 cancels each
other.

Query1 is easy. Simple criterial with count = 1.
For even & odd, I have to use a number or "OR" criteria using count.

Query2
COUNT = 02 OR COUNT = 04, OR COUNT = 06........

Query3
COUNT = 03 OR COUNT = 05, OR COUNT = 07........

Pls notice that in odd, the count should start from 3 and not 1,
I wonder if there is a built-in function in access which can predict this
even & odd array.
Secondly, if not, can we construct a fuction specifically for this kind of
routine.


Would appreciate valuable comments.

 

[Allen Browne]

Try something like the example below. It assumes OrderID is the primary key,
and ClientID is the foreign key.

SELECT ClientID,
Count(OrderID) AS HowMany,
((Count(OrderID) Mod 2) = 0) AS IsEven
FROM tblOrder
GROUP BY ClientID
ORDER BY ((Count(OrderID) Mod 2) = 0), ClientID