Re: SELECT DISTINCT problems

From: "SCAHILL KEVIN" <9726209@student.ul.ie>

> I would like to pull each distinct value of LecturerName with any one
> corresponding ProjectCode value, it does not matter what the ProjectCode
> value is, but all attemps at this have failed so far.
>
> I have tried this but it does not work:
>
> Set rsLecturers = Server.CreateObject("ADODB.Recordset")
> sqlLect = "Select LecturerName, MIN(ProjectCode) from tblSuggestions WHERE
> LecturerName IN ( SELECT DISTINCT LecturerName FROM tblSuggestions)"
> rsLecturers.Open sqlLect, Conn, 3, 3
>
> I get this error when I try to run this:
> [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query
that
> does not include the specified expression 'LecturerName' as part of an
> aggregate function.

The "min()" function means this is what access refers to as a "totals
query". Try something like:

SELECT LecturerName,min(ProjectCode) FROM tblSuggestions GROUP BY
LecturerName

Basically, anything that is not min() or max()ed should be mentioned in the
GROUP BY.

You might need to quote "LecturerName" (like that) etc since they are
mixed-case. On the other hand the ODBC might deal with all that for you.

- Richard Huxton