Using "where" in expression builder

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello there, My ultimate goal is to have a query that has two columns that
return these results:

Column 1: Sum ([total cost]) where [place of service]=21
Column 2: Sum ([total cost]) where [place of service]<>21

However, I haven't had any luck using "where" in the expression.

I know I can get the results for Sum ([total cost]) where [place of
service]=21 by making one query column the sum of total cost, and another
query column the where criteria for the place of service field. But then it
seems like I'd have to make another query altogether for the Sum ([total
cost]) where [place of service]<>21. I'm hoping to be able to do this in one
query somehow. Any suggestions would be great.

I hope that makes sense.
 
Try this ---
SELECT Sum(IIf([place of service]=21,1,0)) AS Expr1, Sum(IIf([place of
service]<>21,1,0)) AS Expr2
FROM YourTable;
 
SELECT DISTINCT
DSum("[total cost]","YourTable","[place of service] = 21") AS TwentyOne,
DSum("[total cost]","YourTable","[place of service]<> 21") AS NotTwentyOne
FROM YourTable ;

Ugly but it works.
 
Sorry, I did not read it carefully.

SELECT IIf([place of service]=21,[total cost],0) AS Expr1, IIf([place of
service]=21,[total cost],0) AS Expr2
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


KARL DEWEY said:
Try this ---
SELECT Sum(IIf([place of service]=21,1,0)) AS Expr1, Sum(IIf([place of
service]<>21,1,0)) AS Expr2
FROM YourTable;

--
KARL DEWEY
Build a little - Test a little


Tom Winn said:
Hello there, My ultimate goal is to have a query that has two columns that
return these results:

Column 1: Sum ([total cost]) where [place of service]=21
Column 2: Sum ([total cost]) where [place of service]<>21

However, I haven't had any luck using "where" in the expression.

I know I can get the results for Sum ([total cost]) where [place of
service]=21 by making one query column the sum of total cost, and another
query column the where criteria for the place of service field. But then it
seems like I'd have to make another query altogether for the Sum ([total
cost]) where [place of service]<>21. I'm hoping to be able to do this in one
query somehow. Any suggestions would be great.

I hope that makes sense.
Click to expand...
Click to expand...
 
Back
Top