UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sorting By Last Name, Access 2016    
 
   
bearcublandon
post Feb 21 2018, 07:14 PM
Post#1



Posts: 12
Joined: 5-December 17



I'm getting the following error message when I try to sort ascending by the last name. Seems to be a conflict with Distinct? How do I resolve this?

"ORDER BY clause [All SCC Ready to Import]/[Last Name] conflicts with Distinct"

The last name isn't used in the report per se, I have a field that combines first and last names, but I want to sort by the last name. In regions where there multiple positions I would like to sort by last name.

Thank you for your help,

Michael


Attached File(s)
Attached File  Sort_by_Last_Name.PNG ( 28.09K )Number of downloads: 15
 
Go to the top of the page
 
RuralGuy
post Feb 21 2018, 07:39 PM
Post#2


UtterAccess VIP
Posts: 2,823
Joined: 25-June 05
From: @ 8300' in the Colorado Rocky Mountains


Create a query that sorts by last name, then base your DISTINCT query on the sorted one.

--------------------
(RG for short) aka Allan Bunch Previous MS Access MVP acXP, ac07, ac10, ac13 - WinXP Pro, Win7 Pro, Win10 Pro
Please reply to the forum so all may benefit.
Go to the top of the page
 
HairyBob
post Feb 22 2018, 04:03 AM
Post#3



Posts: 985
Joined: 26-March 08
From: London, UK


Hi Rural Guy,

Shouldn't that be the other way round... DISTINCT query first (i.e. inner) then ORDER BY query (outer) based on DISTINCT query?:

CODE
SELECT Individual, [Local Leadership Positions], [Local Name] FROM
(SELECT DISTINCT Individual, [Local Leadership Positions], [Local Name], [Last Name] FROM [All SCC Ready to Import]) AS DRV
ORDER BY [Last Name]


Hairy.
Go to the top of the page
 
kfield7
post Feb 22 2018, 08:39 AM
Post#4



Posts: 858
Joined: 12-November 03
From: Iowa Lot


I believe DISTINCT sorts left to right, so both ways could produce the same results if the sorted field is first in the list.
In that case you may not need a separate sorting query.
Otherwise, I agree the sorting query is typically the last (or outer) query.
This post has been edited by kfield7: Feb 22 2018, 08:44 AM
Go to the top of the page
 
bearcublandon
post Feb 27 2018, 06:58 PM
Post#5



Posts: 12
Joined: 5-December 17



Thank you all for the responses. I didn't get an email saying that they've been answered (like in the Mr Excel message forum).
Go to the top of the page
 
DougY
post Feb 27 2018, 10:45 PM
Post#6


Utterly Abby-Normal
Posts: 15,029
Joined: 30-June 04
From: Seattle, WA [USA]


Hi Bearcub,

You should have gotten a notification when a reply is posted to the thread. Few options:

You deselected the the Enable Email notifications of replies (see image)
Make sure we have your current Email
Check your Email spam folder, it case the notification ended up there

Let me know if one of the option fit or not.


Attached File  UACapture.JPG ( 75.49K )Number of downloads: 0



--------------------
- Doug

When I said the program was fool proof, I hadn’t realized there were so many fools.
Go to the top of the page
 
bearcublandon
post Feb 27 2018, 10:50 PM
Post#7



Posts: 12
Joined: 5-December 17



This is the first notification I've ever received thank you. I thought I had email notifications turned on but I obviously didn't. Thank you for turning it on.

MIchael
Go to the top of the page
 
DougY
post Feb 27 2018, 11:11 PM
Post#8


Utterly Abby-Normal
Posts: 15,029
Joined: 30-June 04
From: Seattle, WA [USA]


Not sure I did anything smile.gif

You're welcome. Please ping us if you encounter issues with the notifications, we'll look into it.

--------------------
- Doug

When I said the program was fool proof, I hadn’t realized there were so many fools.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th June 2018 - 10:50 AM