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]

Re: Select distict and order by in a combo box

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" <john@jmawer.de mon.co.uk> wrote in message
news:c05arh$q64 $1$830fa7b3@new s.demon.co.uk.. .[color=blue]
> 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[/color]
with[color=blue]
> the 'Distinct'. Surely it does not?
>
> SELECT DISTINCT [Incidents].[StudentId], [Incidents].[Surname] & " " &
> [Incidents].[Forename] FROM Incidents WHERE [Surname]>"";
>
> Thanks in advance
>
> John M
>
>[/color]

[John M]

Re: Select distinct and order by in a combo box

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_djsteel e@NOSPAM_canada .com> wrote in message
news:klqVb.1153 9$R6H.4794@twis ter01.bloor.is. net.cable.roger s.com...[color=blue]
> 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" <john@jmawer.de mon.co.uk> wrote in message
> news:c05arh$q64 $1$830fa7b3@new s.demon.co.uk.. .[color=green]
> > Hi,
> > The line below is used to feed a combobox. (It is from a database which[/color][/color]
is[color=blue][color=green]
> > 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[/color]
> with[color=green]
> > the 'Distinct'. Surely it does not?
> >
> > SELECT DISTINCT [Incidents].[StudentId], [Incidents].[Surname] & " " &
> > [Incidents].[Forename] FROM Incidents WHERE [Surname]>"";
> >
> > Thanks in advance
> >
> > John M
> >
> >[/color]
>
>[/color]

[Bas Cost Budde]

Re: Select distinct and order by in a combo box

John M wrote:
[color=blue]
> Thanks .....
>
> That's what I had a go at, but I get the error message 'Order by clause
> ([Surname]) conflicts with distinct'
>[/color]
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]

Re: Select distinct and order by in a combo box

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" <bas@heuveltop. org> wrote in message
news:c05ga0$umj $6@news2.solcon .nl...[color=blue]
> John M wrote:
>[color=green]
> > Thanks .....
> >
> > That's what I had a go at, but I get the error message 'Order by clause
> > ([Surname]) conflicts with distinct'
> >[/color]
> 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
>[/color]

[Bas Cost Budde]

Re: Select distinct and order by in a combo box

John M wrote:
[color=blue]
> 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!![/color]

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.Stude ntID, [Incidents].[Surname] & " " &
[Incidents].[Forename] AS Expr1
FROM Incidents
WHERE (((Incidents.Su rname)>""))
ORDER BY [Incidents].[Surname] & " " & [Incidents].[Forename];

--
Bas Cost Budde

http://www.heuveltop.org/BasCB

but the domain is nl

[John M]

Re: Select distinct and order by in a combo box

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" <bas@heuveltop. org> wrote in message
news:c05pin$m1$ 2@news2.solcon. nl...[color=blue]
> John M wrote:
>[color=green]
> > I have just tried the same expression as the SELECT. Same result. I[/color][/color]
thne[color=blue][color=green]
> > tried sorting on a different field (YearGrp) in the same table but not[/color][/color]
used[color=blue][color=green]
> > in the select, and the same result. I've also cut and paste from a[/color][/color]
query in[color=blue][color=green]
> > which I call Surname and Forename AllName. In this too the conflict[/color][/color]
arises.[color=blue][color=green]
> >
> > Despite your help, i'm lost!![/color]
>
> 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.Stude ntID, [Incidents].[Surname] & " " &
> [Incidents].[Forename] AS Expr1
> FROM Incidents
> WHERE (((Incidents.Su rname)>""))
> ORDER BY [Incidents].[Surname] & " " & [Incidents].[Forename];
>
> --
> Bas Cost Budde
> http://www.heuveltop.org/BasCB
> but the domain is nl
>[/color]