Recordset Problem

I have a Recordset as below which works

SELECT DISTINCT MemberID, RegStatus, ProfileDate, Title, PostTown, PostCode
FROM [Site Photos]
ORDER BY ProfileDate DESC

However as soon as I add a further field called "images" it all goes wrong

MemberID may have 3 images attached

How can I do the recordset so that only the first image record for the
members is displayed (as in the top records set)

so I only get 1 record displayed per memberID regardless of how many images
they have

Does that make sense ?

AndrewHi

Something like the following should work.

SELECT S.MemberID, S.RegStatus, S.ProfileDate, S.Title, S.PostTown,
S.PostCode, S.Images
FROM [Site Photos] S
JOIN ( SELECT MemberID, MIN(ProfileDate) AS ProfileDate
FROM [Site Photos] GROUP BY MemberID ) A ON A.MemberID = S.MemberID AND
A.ProfileDate = S.ProfileDate
ORDER BY S.ProfileDate DESC

OR

SELECT S.MemberID, S.RegStatus, S.ProfileDate, S.Title, S.PostTown,
S.PostCode, S.Images
FROM [Site Photos] S
WHERE S.ProfileDate IN ( SELECT MIN(ProfileDate)
FROM [Site Photos] A
WHERE A.MemberID = S.MemberID)
ORDER BY S.ProfileDate DESC

John

"Andrew" <andrew@.nospam.com> wrote in message
news:H_bdb.56$9m.6@.newsfep1-gui.server.ntli.net...
> I have a Recordset as below which works
> SELECT DISTINCT MemberID, RegStatus, ProfileDate, Title, PostTown,
PostCode
> FROM [Site Photos]
> ORDER BY ProfileDate DESC
> However as soon as I add a further field called "images" it all goes wrong
> MemberID may have 3 images attached
> How can I do the recordset so that only the first image record for the
> members is displayed (as in the top records set)
> so I only get 1 record displayed per memberID regardless of how many
images
> they have
> Does that make sense ?
> Andrew