Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-16-2004, 02:57 PM   #1
Maldrid
Junior Member
 
Join Date: Jul 2003
Location: Chicago
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Maldrid
Select Fields but Sort by another Field

I have a query that selects two fields and they must be Distinct, but I want the recordset to come sorted by a field I do not select. Is there a way of accomplishing this doing a sub select? Or some other fashion?


Here is my query that I have right now
Code:
sSQLQuery = "SELECT DISTINCT [Bill Code], [Price Type], [Reference Code] FROM CBill" & sCBILL & " WHERE [KitchenID] = '" & sKitchenID_P _
                    & "' AND [Customer ID] = '" & sCustomerID_P _
                    & "' AND [Flight #] = '" & rsFlight("Flight #") _
                    & "' AND [Flight # Seq] = '" & rsFlight("Flight # Seq") _
                    & "' AND [Segment #] = '" & rsFlight("Segment #") _
                    & "' AND [Flight Date] BETWEEN #" & sFromDate_P & "# AND #" & sToDate_P _
                    & "# AND [Recap Type] = '" & sRecapType_P _
                    & "' AND [Account Code] = '" & rsAccount("Account Code") & "' ORDER BY [SEQ #]"
Now obviously this query will not work because I am selecting Bill Code and Price Type and trying to sort by SEQ #, but is there a way of doing this?

Maldrid is offline   Reply With Quote
Old 02-16-2004, 09:09 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,303
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
No. The query summarizes away everything but the selected columns. Rather than using Distinct, you might want to use Group By. In that case, you can add Max([Seq #]) to obtain one Seq # for each unique instance of [Bill Code], [Price Type], [Reference Code]

BTW, it is poor practice to use embedded spaces or special characters in your column or other object names.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 02-17-2004, 07:49 AM   #3
Maldrid
Junior Member
 
Join Date: Jul 2003
Location: Chicago
Posts: 20
Thanks: 0
Thanked 0 Times in 0 Posts
Maldrid
Thanks I will try to mess with that.

Thanks for the tip. It is just that this is a huge database and I already wrote tons of code for it that it would take me a very very long time to fix this. I will remember for the future.

Maldrid is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 07:32 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World