Select Fields but Sort by another Field (1 Viewer)

Maldrid · Feb 16, 2004

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?

Pat Hartman · Feb 17, 2004

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.

Maldrid · Feb 17, 2004

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.

Select Fields but Sort by another Field (1 Viewer)

Maldrid

Registered User.

Pat Hartman

Super Moderator

Maldrid

Registered User.

Similar threads

Users who are viewing this thread