Could someone please help me with a MS access Query to do some custom grouping and sorting? I suspect I need to just drop down to the SQL and program it, but up until now, I've only built queries using the normal Access design screen.
I just need the query to group and sort the records in a certain order. Then I can use the query to feed the reports.
The table is "item-table" and the field I need to sort is "reg_no". The reg. numbers can be alpha-numeric, so it's set as a Text field. Here is an example of some of the registration numbers and the order I need them to sort in.
600
725
999
1000
2500
5CA
20CA
7NA
49NA
2AB
10AB
40AB
So, I need all the ones that are just numbers first, and within that sub-group, they need to be in numerical order. Then coming next in the sorted list, the ones with CA at the end, also in numerical order. Followed by the ones that have NA at the end, and so on. There are more groups than this, but I think I can figure out how to insert the rest of them into a query or SQL statement that's working with this cross section of records.
This works for ONLY the 3 digit numbers:
SELECT [item-table].reg_no
FROM [item-table]
WHERE ((([item-table].reg_no) Like "###"))
ORDER BY [item-table].reg_no;
But I don't know how to modify it for the 4 digit numbers (1000-9999), then the CA series, NA series, etc... all from the same query. Is this the right approach or is there a better way?
Any help or push in the right direction will be greatly appreciated.
I just need the query to group and sort the records in a certain order. Then I can use the query to feed the reports.
The table is "item-table" and the field I need to sort is "reg_no". The reg. numbers can be alpha-numeric, so it's set as a Text field. Here is an example of some of the registration numbers and the order I need them to sort in.
600
725
999
1000
2500
5CA
20CA
7NA
49NA
2AB
10AB
40AB
So, I need all the ones that are just numbers first, and within that sub-group, they need to be in numerical order. Then coming next in the sorted list, the ones with CA at the end, also in numerical order. Followed by the ones that have NA at the end, and so on. There are more groups than this, but I think I can figure out how to insert the rest of them into a query or SQL statement that's working with this cross section of records.
This works for ONLY the 3 digit numbers:
SELECT [item-table].reg_no
FROM [item-table]
WHERE ((([item-table].reg_no) Like "###"))
ORDER BY [item-table].reg_no;
But I don't know how to modify it for the 4 digit numbers (1000-9999), then the CA series, NA series, etc... all from the same query. Is this the right approach or is there a better way?
Any help or push in the right direction will be greatly appreciated.
Last edited: