Prev: Date format
Next: update two fields at once
From: Angela on 23 Jan 2010 02:09
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.


From: Allen Browne on 23 Jan 2010 06:24
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


--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Angela" <imsguy(a)gmail.com> wrote in message
news:ela22xAnKHA.4628(a)TK2MSFTNGP06.phx.gbl...
> 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.
>
 | 
Pages: 1
Prev: Date format
Next: update two fields at once