query problem (lack of knowledge on my part)

Hello,

I've been trying for a couple of days now with the following problem in SQL

Ive a table called tblRresults that has the following

Date - just a regular date

OfficeLocation - just a unique id such as a zip code

SampleType - contains things like wall, floor, beam

SampleResult - "Detected" or "NotDetected"

When populated the table can look like

12/12/05 99505 Wall Detected

12/12/05 99505 Wall NotDetected

10/04/05 99211 Beam Detected

10/04/05 99211 Beam Detected

10/04/05 99111 Floor NotDetected

10/04/05 99111 Floor NotDetected

What I want is (I guess) a crosstab query that produces the following

Date Location SampleType CountDetected CountNotDetected

12/12/05 99505 Wall 1 1

10/04/05 99211 Beam 2 0 (or null would be ok)

10/04/05 99111 Floor 0 2

Any help gratefully accepted, this has been driving me mental!

thanks

Select Date,Location,SampleType,SUM(CASE WHEN SampleResult='Detected' THEN 1 ELSE 0 END) AS CountDetected,SUM(CASE WHEN SampleResult='NotDetected' THEN 1 ELSE 0 END) AS CountNotDetected

FROM table

GROUP BY Date,Location,SampleType

|||thanks very much! - I had no idea CASE existed