Advanced Sorting

[Melagan]

Greetings,

Example data in desired sort order from query:

OrderID  PDC_Rep      PDC         NDC_Rep    NDC
1234     Joe Smith    Al Johnson  Shane Doe  Ron Whit
1237     Joe Smith    Bob Thorn   Joe Smith  Bob Thorn
1233     Joe Smith    Carl Smith  Phil Jones Zed Xy
1238     Phil Jones   Zed Xy      Joe Smith  Al Johnson
1239     Shane Doe    Ron Whit    Joe Smith  Al Johnson
1230     Phil Jones   Jeff Wright Joe Smith  Anton Notan
1240     John Doe     Jim East    Joe Smith  Bob Thorn
1241     Shane Doe    Bill Smith  Joe Smith  Carl Smith
1242     Phil Jones   Anton Notan Joe Smith  Don Hersch

This is pretty hard to explain and probably even more difficult to accomplish. This query is really about "Joe Smith". The basic SQL is as such:

SELECT t.OrderID, t.PDC_Rep, t.PDC, t.NDC_Rep, t.NDC
FROM tblMyTable as t
WHERE t.PDC_Rep = "Joe Smith" OR t.NDC_Rep = "Joe Smith"
ORDER BY _____help_____

I need the first "group" of records to be when Joe Smith is PDC_Rep, and within that group, I want the PDC to be sorted alphabetically; Now when Joe Smith is NDC_Rep, I'd like to sort those NDC's alphabetically as well.

I have tried playing around with IIF statements to give me something numeric to sort by, such as:

xSort: IIF([PDC] = "Joe Smith", 0, 100)
ySort: IIF([NDC] = "Joe Smith", 1, 100)

That works great in grouping the PDC_Rep and NDC_Rep sections together, but not sure how to get the PDC and NDC fields sorted alphabetically WITHIN those groupings.

I'm not too great with UNION queries yet, but I'm thinking that is the direction I need to look at. Additionally, this data will eventually appear in a report, which is where the sort-order is required. If there are any other sorting options that are available within the report, that would obviously be a good option as well.

Any ideas, suggestions? Thanks in advance, as always.




~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

SELECT OrderID, PDC_Rep, PDC, NDC_Rep, NDC
FROM tblMyTable
WHERE 'Joe Smith' In (PDC_Rep, NDC_Rep)
ORDER BY IIf([PDC_Rep] = 'Joe Smith', 0, 1), IIf([PDC_Rep] = 'Joe Smith', [PDC], [NDC])


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Perhaps simpler:
SELECT OrderID, PDC_Rep, PDC, NDC_Rep, NDC
FROM tblMyTable
WHERE 'Joe Smith' In (PDC_Rep, NDC_Rep)
ORDER BY IIf([PDC_Rep] = 'Joe Smith', '0' & [PDC], '1' & [NDC])


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Melagan]

This looks very promising - I havn't had a chance to play with it yet but should have some feedback tomorrow!

~Melagan
______
"It's never too late to become what you might have been.

 

[MichaelRed]

unfortunatly, sorting a recordset for use in a report is usually a waste of time. ANY sorting / grouping in a report will reorder (sort) the source recorset to match the sorting grouping requirements, totally ignoring the original ordering. While the soloution provided by PHV is quite reasonable for the query, it will not help the report if you use any group header or footer. Alos, note that utilizing the group headers / footers in the report will provide the same result and the opportunity to provide a much more professional looking report.





MichaelRed

 

[PHV]

Who talked about a report ?

 

[Melagan]

Last paragraph in my OP =)

~Melagan
______
"It's never too late to become what you might have been.

 

[Melagan]

PHV, your second post seems to be the best solution; it works great!

As far as the report goes - I just wanted to mention that this recordset was eventually going to be in a report, and wanted to open up report sorting options as a way to get the data sorted the way I want. I won't be needing group headers/footers for this particular report, so the SQL sorting should be just fine.

Thanks again, cheers!

~Melagan
______
"It's never too late to become what you might have been.

 

[PHV]

Additionally, this data will eventually appear in a report
OOps :~/