Select distict and order by in a combo box

[John M]

Hi,
The line below is used to feed a combobox. (It is from a database which is
used to log pupil behaviour!) The 'incidents' table contains a list of
students who have been involved in incidents. Some may appear several
times, hence the Distinct. However, thelist generated should still be
sorted by Surname. When I add Order by [Surname] I'm told it conflict with
the 'Distinct'. Surely it does not?

SELECT DISTINCT [Incidents].[StudentId], [Incidents].[Surname] & " " &
[Incidents].[Forename] FROM Incidents WHERE [Surname]>"";

Thanks in advance

John M

[Douglas J. Steele]

It should be possible. What's the SQL you're trying to use?

It should be
SELECT DISTINCT [Incidents].[StudentId], [Incidents].[Surname] & " " &
[Incidents].[Forename] FROM Incidents WHERE [Surname]>""
ORDER BY [Surname]
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"John M" <jo**@jmawer.demon.co.uk> wrote in message
news:c0*******************@news.demon.co.uk...

Hi,
The line below is used to feed a combobox. (It is from a database which is
used to log pupil behaviour!) The 'incidents' table contains a list of
students who have been involved in incidents. Some may appear several
times, hence the Distinct. However, thelist generated should still be
sorted by Surname. When I add Order by [Surname] I'm told it conflict with the 'Distinct'. Surely it does not?

SELECT DISTINCT [Incidents].[StudentId], [Incidents].[Surname] & " " &
[Incidents].[Forename] FROM Incidents WHERE [Surname]>"";

Thanks in advance

John M

[John M]

Thanks .....

That's what I had a go at, but I get the error message 'Order by clause
([Surname]) conflicts with distinct'
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:kl******************@twister01.bloor.is.net.c able.rogers.com...

It should be possible. What's the SQL you're trying to use?

It should be
SELECT DISTINCT [Incidents].[StudentId], [Incidents].[Surname] & " " &
[Incidents].[Forename] FROM Incidents WHERE [Surname]>""
ORDER BY [Surname]
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

"John M" <jo**@jmawer.demon.co.uk> wrote in message
news:c0*******************@news.demon.co.uk...

Hi,
The line below is used to feed a combobox. (It is from a database which is used to log pupil behaviour!) The 'incidents' table contains a list of
students who have been involved in incidents. Some may appear several
times, hence the Distinct. However, thelist generated should still be
sorted by Surname. When I add Order by [Surname] I'm told it conflict

with

the 'Distinct'. Surely it does not?

SELECT DISTINCT [Incidents].[StudentId], [Incidents].[Surname] & " " &
[Incidents].[Forename] FROM Incidents WHERE [Surname]>"";

Thanks in advance

John M

[Bas Cost Budde]

John M wrote:

Thanks .....

That's what I had a go at, but I get the error message 'Order by clause
([Surname]) conflicts with distinct'

This is Access dialect. Either include Surname as separate field, or
ORDER BY the same expression as you SELECT:

ORDER BY [Incidents].[Surname] & " " & [Incidents].[Forename]

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[John M]

I have just tried the same expression as the SELECT. Same result. I thne
tried sorting on a different field (YearGrp) in the same table but not used
in the select, and the same result. I've also cut and paste from a query in
which I call Surname and Forename AllName. In this too the conflict arises.

Despite your help, i'm lost!!

"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:c0**********@news2.solcon.nl...

John M wrote:

Thanks .....

That's what I had a go at, but I get the error message 'Order by clause
([Surname]) conflicts with distinct'

This is Access dialect. Either include Surname as separate field, or
ORDER BY the same expression as you SELECT:

ORDER BY [Incidents].[Surname] & " " & [Incidents].[Forename]

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[Bas Cost Budde]

John M wrote:

I have just tried the same expression as the SELECT. Same result. I thne
tried sorting on a different field (YearGrp) in the same table but not used
in the select, and the same result. I've also cut and paste from a query in
which I call Surname and Forename AllName. In this too the conflict arises.

Despite your help, i'm lost!!

Then, by all means do a Totals query and group on the name expression.

(No, I can't stand this)

The Help says:
U hebt een SQL-instructie gemaakt met een predikaat ALL, DISTINCT of
DISTINCTROW en een component ORDER BY die een veld bevat dat niet
voorkomt in de instructie SELECT. Verwijder het gereserveerde woord
DISTINCT, of verwijder het opgegeven veld uit de component ORDER BY.

(that's Dutch! Let's try to translate)

You created a SQL instruction with a predicate ALL, DISTINCT or
DISTINCTROW and a component ORDER BY with a field that is not in the
SELECT instruction. Remove the word DISTINCT or remove the field from
the component ORDER BY.

With this information, I removed Surname from the ORDER BY. This is my
result that runs without error:

SELECT DISTINCT Incidents.StudentID, [Incidents].[Surname] & " " &
[Incidents].[Forename] AS Expr1
FROM Incidents
WHERE (((Incidents.Surname)>""))
ORDER BY [Incidents].[Surname] & " " & [Incidents].[Forename];

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

[John M]

I think my last message was not delivered. If it was, apologies for the
duplication.

Excellent - problem solved - many thanks. I still don't understand it, it
seems to hang around the 'Where' clause and the brackets. That is the only
difference between what you sent me and attempts I have made. (So somewhere
it is still a challenge.

Thanks again

John M
"Bas Cost Budde" <ba*@heuveltop.org> wrote in message
news:c0*********@news2.solcon.nl...

John M wrote:

I have just tried the same expression as the SELECT. Same result. I thne tried sorting on a different field (YearGrp) in the same table but not used in the select, and the same result. I've also cut and paste from a query in which I call Surname and Forename AllName. In this too the conflict arises.
Despite your help, i'm lost!!

Then, by all means do a Totals query and group on the name expression.

(No, I can't stand this)

The Help says:
U hebt een SQL-instructie gemaakt met een predikaat ALL, DISTINCT of
DISTINCTROW en een component ORDER BY die een veld bevat dat niet
voorkomt in de instructie SELECT. Verwijder het gereserveerde woord
DISTINCT, of verwijder het opgegeven veld uit de component ORDER BY.

(that's Dutch! Let's try to translate)

You created a SQL instruction with a predicate ALL, DISTINCT or
DISTINCTROW and a component ORDER BY with a field that is not in the
SELECT instruction. Remove the word DISTINCT or remove the field from
the component ORDER BY.

With this information, I removed Surname from the ORDER BY. This is my
result that runs without error:

SELECT DISTINCT Incidents.StudentID, [Incidents].[Surname] & " " &
[Incidents].[Forename] AS Expr1
FROM Incidents
WHERE (((Incidents.Surname)>""))
ORDER BY [Incidents].[Surname] & " " & [Incidents].[Forename];

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl