Queries: Where and expression

[mohsin via AccessMonster.com]

Hi there

As an example, my input table and output result as below

Date AA BB AA(1+2) AA(3+4) AA
(5+6)
090308 1 0.2 0.6 1.1
1.5
090308 2 0.4 > Output
090308 3 0.5
090308 4 0.6
090308 5 0.7
090308 6 0.8

SELECT [Input].DATE, Sum(([BB])) AS [AA(1+2)]
FROM [Input]
WHERE ((([Input]![AA])="1" Or ([Input]![AA])="2"))
GROUP BY [Input].DATE;

Any idea how can I query for AA(3+4) and AA(5+6)?

Appreciate for your help

 

[John Spencer]

SELECT [Date]
, Sum(IIF(AA in (1,2),BB,Null) as FirstSum
, Sum(IIF(AA in (3,4),BB,Null) as SecondSum
, Sum(IIF(AA in (5,6),BB,Null) as ThirdSum
FROM [Input]
GROUP BY [Date]

IF you are always grouping by pairs of consecutive numbers (1,2 ; 3,4;
5,6) then you could use something like the following

SELECT [Date]
, !+((AA-1)\2) as PairNumber
, Sum(BB) as TheSum
FROM [Input]
GROUP BY [Date]
, !+((AA-1)\2)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[John Spencer]

Typo in the second query - Managed to put in an exclamation point
instead of a one.

SELECT [Date]
, 1 +((AA-1)\2) as PairNumber
, Sum(BB) as TheSum
FROM [Input]
GROUP BY [Date]
, 1 +((AA-1)\2)


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

SELECT [Date]
, Sum(IIF(AA in (1,2),BB,Null) as FirstSum
, Sum(IIF(AA in (3,4),BB,Null) as SecondSum
, Sum(IIF(AA in (5,6),BB,Null) as ThirdSum
FROM [Input]
GROUP BY [Date]

IF you are always grouping by pairs of consecutive numbers (1,2 ; 3,4;
5,6) then you could use something like the following

SELECT [Date]
, !+((AA-1)\2) as PairNumber
, Sum(BB) as TheSum
FROM [Input]
GROUP BY [Date]
, !+((AA-1)\2)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi there

As an example, my input table and output result as below

Date AA BB AA(1+2)
AA(3+4) AA
(5+6)
090308 1 0.2 0.6 1.1
1.5
090308 2 0.4 > Output
090308 3 0.5
090308 4 0.6
090308 5 0.7
090308 6 0.8

SELECT [Input].DATE, Sum(([BB])) AS [AA(1+2)]
FROM [Input]
WHERE ((([Input]![AA])="1" Or ([Input]![AA])="2"))
GROUP BY [Input].DATE;

Any idea how can I query for AA(3+4) and AA(5+6)?

Appreciate for your help