[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
Solved

# Access Query: Sorting by a hyphen

Posted on 2006-05-04
Medium Priority
402 Views
Hi:
I left another question the other day about being able to sort and perhaps group a field with and without a hyphen.  Now that some others helped me refine a part of the following query, I'm posting this again.  My query looks like this:

SELECT INS.REC, INS.NAME, Sum(tblINS_POLICY_Temp_3.CO_AMT) AS SumOfCO_AMT
FROM INS INNER JOIN tblINS_POLICY_Temp_3 ON INS.REC = tblINS_POLICY_Temp_3.ShortCode
GROUP BY INS.REC, INS.NAME
HAVING (INS.REC<>"NLVCO-1") and (INS.REC<>"TRAIL-1")  and (INS.REC<>"TEAM3-2") and (INS.REC<>"TEAM3-1") and (INS.REC<>"TEST001")
ORDER BY Sum(tblINS_POLICY_Temp_3.CO_AMT) DESC;

I need every INS.REC code with a hyphen in it to be grouped separately from all INS.REC codes without a hyphen.

Charlie

Once again, points for urgency
0
Question by:cepes
• 2

LVL 8

Accepted Solution

Pigster14 earned 2000 total points
ID: 16609615
SELECT INS.REC, INS.NAME, Sum(tblINS_POLICY_Temp_3.CO_AMT) AS SumOfCO_AMT, IIf(InStr(1,INS.REC,"-")>0,1,2) AS SortOrder
FROM INS INNER JOIN tblINS_POLICY_Temp_3 ON INS.REC = tblINS_POLICY_Temp_3.ShortCode
GROUP BY INS.REC, INS.NAME,IIf(InStr(1,INS.REC,"-")>0,1,2)
HAVING (INS.REC<>"NLVCO-1") and (INS.REC<>"TRAIL-1")  and (INS.REC<>"TEAM3-2") and (INS.REC<>"TEAM3-1") and (INS.REC<>"TEST001")
ORDER BY IIf(InStr(1,INS.REC,"-")>0,1,2), Sum(tblINS_POLICY_Temp_3.CO_AMT) DESC;

Try this. May have to switch ORDER BY around depending what order you want.

Thanks
0

Author Comment

ID: 16609637
Pigster14 (great name!):
That worked!  How can I also group by these results?

Charlie
0

LVL 8

Expert Comment

ID: 16613815
Thanks. I used to raise pigs, I collect pig things, love pigs. Reason for name.
Did including it in the Group By clause not help?

Thanks.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
###### Suggested Courses
Course of the Month20 days, 14 hours left to enroll