Case When Where 1

[Skie]

I'm using an Access database and I'd like to have a query use different WHERE statements based on the area. I'm fairly new to SQL, so I don't know if a case statement can do this or if I'm using it correctly. I could make two queries and join them in the third, but that seems like poor solution. If it's possible to make a single SQL query to handle this I'd appreciate it.

SELECT
    tblTemp.State,
    tblTemp.Person,
    tblTemp.Occupation
FROM
CASE tblTemp.Area
    WHEN 'NY' THEN
        WHERE
            tblTemp.Occupation<>''
    ELSE
        WHERE
            tblTemp.Occupation<>'' AND
            tblTemp.Occupation<>'Sales'
END 
GROUP BY tblTemp.State, tblTemp.Occupation;

 

[mrdenny]

You need to do something along these lines.

SELECT
    tblTemp.State,
    tblTemp.Person,
    tblTemp.Occupation
FROM
where (tblTemp.Area = 'NY' and tmpTemp.Occupation <> '')
	and (tblTemp.Area <> 'NY' and tblTemp.Occupation <> '' and tblTemp.Occupation <> 'Sales')
GROUP BY tblTemp.State, tblTemp.Occupation;

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Skie]

That's the perfect solution.

Thanks.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com