Multiple unique records by date 2

[SmallTime]

I have a table (Tbl_notes) containing amongst other fields
RefId
NoteID
NoteDate
NoteType

Looks something like this

RefID NoteID NoteDate NoteType
1 1 12/01/2005 Response1
1 2 13/01/2005 Response1
1 3 12/01/2005 Response2
2 4 12/01/2005 Response1
2 5 13/01/2005 Response1
2 6 14/01/2005 Response1
3 7 12/01/2005 Response1
3 8 12/01/2005 Response2

I want to create a query to show all 4 fields but only where NoteType = Response1 and only the first response1 by NoteDate

So the end result would look like this

RefID NoteID NoteDate NoteType
1 1 12/01/2005 Response1
2 4 12/01/2005 Response1
3 7 12/01/2005 Response1

I’ve been at this all morning but can’t work out how it would be done.

Many thanks.

 

[PHV]

something like this (SQL code) ?
SELECT A.RefID, A.NoteID, A.NoteDate, A.NoteType
FROM Tbl_notes AS A INNER JOIN (
SELECT RefID, Min(NoteDate) AS FirstNote FROM Tbl_notes WHERE NoteType='Response1' GROUP BY RefID
) AS B ON A.RefID = B.RefID AND A.NoteDate = B.FirstNote
WHERE A.NoteType='Response1'

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

 

[r937]

select RefID 
     , NoteID 
     , NoteDate         
     , NoteType
  from Tbl_notes as T
 where NoteType = 'Response1'
   and NoteDate =
       ( select min(NoteDate)
           from Tbl_notes
          where NoteType = 'Response1'
            and RefID = T.RefID )

r937.com | rudy.ca

 

[SmallTime]

Thank you both so much.

I'd never have managed it myself

 

[SmallTime]

One further question.

I’m trying to incorporate the same method into another table which unfortunately as a field with a space eg ‘Organisation name’ how would I handle this?

Regards

 

[PHV]

[!][[/!]Organisation name[!]][/!]

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