Prev: Date formatNext: 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" 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 formatNext: update two fields at once