Recordset Problem

[Andrew]

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

[John Bell]

Re: Recordset Problem

Hi

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$9 m.6@newsfep1-gui.server.ntli .net...[color=blue]
> I have a Recordset as below which works
>
> SELECT DISTINCT MemberID, RegStatus, ProfileDate, Title, PostTown,[/color]
PostCode[color=blue]
> 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[/color]
images[color=blue]
> they have
>
> Does that make sense ?
>
> Andrew
>
>[/color]