Convert this to an access query?

[Bell1991]

I have the following t-sql query:

Select
sum(s.numRecords) as numTotalRecords ,
sum(s.numHomePri) as numHomePri ,
sum(s.numHomeSec) as numHomeSec

from tblHousingIDA c,
(Select
1 as numRecords ,
case when numHomeID = 2 then 1 else 0 end numHomePri ,
case when numHomeID = 3 then 1 else 0 end numHomeSec
from tblHousingIDA )s

Is there a way to convert this to access?
I get an error by the case statement?

Thanks,
-Bell

 

[dhookom]

It seems to me that this would create a cartesian query in either T-SQL or Access. You can get the same results in Access using:
Select
sum(s.numRecords) as numTotalRecords ,
sum(s.numHomePri) as numHomePri ,
sum(s.numHomeSec) as numHomeSec

from tblHousingIDA c,
(Select
1 as numRecords ,
IIf(numHomeID = 2,1,0) As numHomePri ,
IIf(numHomeID = 3,1,0) As numHomeSec
from tblHousingIDA )s


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Bell1991]

Thanks - it is somewhat working (your code is correct my original is not)

SInce the tables reference the same the table i am getting for the result x times the amount (for example if there are 10 records in the table and one record has 2 for numHomeID - then i am getting back 10.

I have tried putting a where clause in it:

where x.uniqueID = x.uniqueID - but it prompte me for a unique id - however, the results are correct?

 

[PHV]

And what about this ?
SELECT
Count(*) as numTotalRecords ,
Sum(IIf(numHomeID=2,1,0)) AS numHomePri ,
Sum(IIf(numHomeID=3,1,0)= AS numHomeSec
FROM tblHousingIDA

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Bell1991,
Your results were the "Cartesian" query that I mentioned in my previous post. I didn't know if that was the desired output.

PHV's solution should work (with missing right paren after [red]numHomeID=3,1,0)[/red])or
SELECT
Count(*) as numTotalRecords ,
Sum(Abs(numHomeID=2)) AS numHomePri ,
Sum(Abs(numHomeID=3))= AS numHomeSec
FROM tblHousingIDA;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Bell1991]

Thanks for all your help..

It works

 

[PHV]

Good catch Duane, thanks for correcting my typo.