Query for Sorting serial numbers

Tesla

New member
Local time
Today, 10:04
Joined
May 1, 2004
Messages
6
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.
 
Last edited:
You can build a table to hold the sorted Reg_Nos and run a series of queries to append the Reg_Nos to the table.

See the table "ItemSorted" and the queries in the database attached. You can run the queries by clicking on the command button on the form.
 

Attachments

Jon K said:
You can build a table to hold the sorted Reg_Nos and run a series of queries to append the Reg_Nos to the table.

See the table "ItemSorted" and the queries in the database attached. You can run the queries by clicking on the command button on the form.

That could be an awful lot of queries. You would have to run a different query for every possible scenario.

I have a couple of functions that would work along with some SQL and do it all automatically but I can't figure how to call the function from a query.

THe idea goes something like this:

Pad the text field to give it a uniform length; Split this into 2 strings (1 Alpha & 1 Numeric) ; Sort the strings independantly to get them into the correct order at the same time retaining the integraty.

I have the functions and I have the SQL that should do it I just can't work out how to put the two together.

I've posted another thread to try to resolve this (I'm sure it is simple and just me being dim---again) and I'll post my, possible, solution when this is completed. -------- Interesting prob though.

Meanwhile Tesla;

Tesla said:
SELECT [item-table].reg_no
FROM [item-table]
WHERE ((([item-table].reg_no) Like "###"))
ORDER BY [item-table].reg_no;

One thing you do need to do though is get rid of the special characters in your naming i.e. item-table would be better as tblItem. Access gets a bit mardy when you do things like that.
If you just wanted to increase the number of digits in your sort query, just increase the number of #'s
 
Last edited:
Excellent work!

Jon K:
Works like a charm! Your SQL to parse the records, select the proper batch, and then number them is great. Your VB to run the various queries in the proper order also works nicely (so that's how you run a group of queries at once!).

Parker:
Well, actually it's like 20 groups. There is a definate set and it's not going to change. The fact that they are alpha-numeric, and variable length isn't helping.

Yes, interesting problem. At first I thought the answer might be one SQL query with sub-queries, or maybe some VB. But, since Jon's suggestion seems to work fine, I'll probably just go with that. I guess it just shows that there is usually several ways to handle a programming procedure.

Anyone who needs to put alpha-numeric registration or serial numbers in numerical order has probably come across this challenge.

About the field names, I thought I was being smart by using - and _, but apparently they are handled like a space anyway. Using [ ] around them seems to be the fix. Now I know why people name like MySortedTable. Well, I guess that's how you learn, right?

Thanks again everyone. This forum has turned out to be an great Access resourse for me.
 
Last edited:
Parker,

Padding and splitting is a great idea! It would work so long as the Alpha string uniformly consists of two characters.

If you plan to use nesting IIFs to sort the Alpha string into the required order, then I would suggest using a table to hold the order of the Alpha string instead. I don't think we can nest 20 IIFs in an expression.

Using padding, splitting and a table to hold the order of Alpha string, I think you can just do it in a query, without the need to create any functions.

Jon
 
Jon K said:
Parker,

Padding and splitting is a great idea! It would work so long as the Alpha string uniformly consists of two characters.

If you plan to use nesting IIFs to sort the Alpha string into the required order, then I would suggest using a table to hold the order of the Alpha string instead. I don't think we can nest 20 IIFs in an expression.

Using padding, splitting and a table to hold the order of Alpha string, I think you can just do it in a query, without the need to create any functions.

Jon

Yes you could, in theory, do it just in a query but allowing for every possible combo would be a nightmare which is why I sugested using a couple of functions and call them from the SQL. I don't agree with your first statement though. There is no reason why you could only do this with a uniform alpha string just the same as it dosn't mater about the lenght of the neumeric string. If both are padded to a uniform length then the padding is just removed after sorting it all out.

I'll keep working at it and see what happens if I resolve the problems I'm having with UDF calls then I will see how it pans out.
 
One more time... My fault.

I made a small mistake in the description of the serial numbers. It turns out there was one more type of number that was un-documented but vital. Because we aren't accounting for it yet, these few records are being excluded from the sort.

In the first "only numbers" series (Query2 in your example), some might have an E at the end, but they still need to be inserted with the rest in that first sub-group. The easiest way to explain is to show you.

600
725
999
1000
1000E
1001
1002E
2500
5CA
20CA
7NA
49NA
2AB
10AB
40AB
2EB
25ED

This is the one we need to modify:

INSERT INTO ItemSorted
SELECT [Item-Table].[Reg_No] AS Reg_No
FROM [Item-Table]
WHERE isNumeric(Reg_No)
ORDER BY cLng([Reg_No]);

Some things I noticed about the records overall (maybe this will help you find these special records). Only a 3 or 4 digit number will ever be followed by a single letter (in this case an E). The numbers in all other groups are less than 99. There are 19 other sub-groups but they end in 2 letters(and 2 of them do have E's... as in EB and ED) but I already built their queries, and they are fine. The repeating numbers, as in 1000 and 1000E, will be rare but it might occur, so please try to include this possibility if at all possible.

I hope to be able to stick with the basic way we are doing this (with the multiple queries, etc.) because it's working very good overall, and it's easy enough for me to understand (barely :-). Using the Append table is also working well as a data source for the reports.

Also, can you point me to a place to research some of these functions, and their syntax, like isNumeric, cLng, Len? If they show up in Access2000 Help at all, the text is very limited. I did find this site, but do you know a better one?

http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/fa-fz_15.htm

Thanks in advance.
 
Last edited:
I have modified Query2 for the ending E and added the queries for EB and ED in the database.


I think the best way to learn the syntax of a function is by typing the name of the function in a module and pressing F1 e.g. when you type:-
isnumeric(

Access will immediately display:-
IsNumeric(Expression) as Boolean

From this you know IsNumeric will evaluate an expression and return a boolean value of either True or False.

If you place the cursor on the word isnumeric and press F1, the help for IsNumeric will normally pop up. You can click on See Also to read the help for some similar functions.


If Access can't display the help when you press F1, you may need to follow the WORKAROUND set out in this Microsoft KB article:-
http://support.microsoft.com/default.aspx?scid=kb;en-us;249065&Product=acc2000

The help files in Access 97 are far better than those in later versions.
 

Attachments

Last edited:
Perfect. Thanks!

Jon,

Thanks so much for all your work. It works perfectly.

Also, thanks for that Access Help fix. I'm running Access2000 (SR-1a) on WinXP and my Help has been doing these things lately (blank window, locking-up, saying I needed to install something). Anyway, I needed the fix.
 
Last edited:
Jon,

I need to sort a Code field in a similar fashion. The Alpha part of code varies from 2 to 4 characters and may include a numeric digit eg

Code
1212
2383
3356
59BB
62BB
114BB
33AA3C
56AA3C
991B2DX
998B2DX
261ALL
268ALL
44AL2
103AL2

There are over 80 Alpha groups. I don't know how to write functions, so if it's possible, I want to do it in a query.

So following what you said:
"..... Using padding, splitting and a table to hold the order of Alpha string, I think you can just do it in a query, without the need to create any functions.",

I have put the Alpha strings in the correct order in a table "tblSortOrder". But I just don't know how to use that table to sort the code field in my query with padding and splitting.

If it's possible, can you show me how? Would it really be a nightmare to do it in one query as Parker said?

Thanks in advance.
 
Last edited:
May,

As the Alpha strings vary in length, you will need a query to first separate the numeric and Alpha strings. So you will need a series of two queries.

I find that using CLng() is simpler than using padding.

For those codes that don't have an Alpha string, I used an underscore _ in the table tblSortOrder and in the first query.

The attached database contains these two queries. You can run the second one:

Query "qryOne":
Code:
SELECT [tblData].Code, 
CLng(IIf(isNumeric(Code), Code,
IIf(IsNumeric(Left(Code,Len(Code)-1)),Left(Code,Len(Code)-1),
IIF(IsNumeric(Left(Code,Len(Code)-2)),Left(Code,Len(Code)-2),
IIF(IsNumeric(Left(Code,Len(Code)-3)),Left(Code,Len(Code)-3),
Left(Code,Len(Code)-4)))))) AS Num, 
IIf(IsNumeric(Code),"_",Right(Code,Len(Code)-Len(Num))) AS Alpha
FROM tblData;
Query "qryTwo":
Code:
SELECT qryOne.Code
FROM qryOne INNER JOIN tblSortOrder ON qryOne.Alpha = tblSortOrder.Alpha
ORDER BY tblSortOrder.SortOrder, qryOne.Num;

I don't believe building them is a nightmare, just a few IsNumeric, Len and Left functions inside some IIFs.

If the Alpha strings are of the same length, it is even simpler as we don't need the IIFs and we can do it in just one query.

Jon
 

Attachments

Last edited:
Interesting Problem. Easy Fix

I came across an interesting problem, but the fix was pretty easy. I noticed that every once-in-while the items weren't getting sorted and appended to the table properly (like 1 in every 7 runs).

To fix it, I just added the following commands to save the table between executing Queries. Is there a way to do this "silently" or hidden and maybe to it will execute a little faster?

- Example Code -

CurrentDb.Execute "Query0"

DoCmd.OpenTable "ItemSorted"
DoCmd.Save acTable, "ItemSorted"
DoCmd.Close acTable, "ItemSorted"

CurrentDb.Execute "Query1"

DoCmd.OpenTable "ItemSorted"
DoCmd.Save acTable, "ItemSorted"
DoCmd.Close acTable, "ItemSorted"

CurrentDb.Execute "QueryCA"

.... etc.

Instead of running it as only a separate "Update Utility", I'm concidering also running it from Reports Event OnOpen, and maybe from some forms.

Hey Jon, if you see this, I was just checking out your reply to May above. You are definitely a master of this SQL code and query sorting business!
 
Last edited:
Definitely not a master. Just fond of solving problems using queries and VBA.

_
I came across an interesting problem ......... I noticed that every once-in-while the items weren't getting sorted and appended to the table properly (like 1 in every 7 runs).
I have never had this problem as I have never had to run a batch of append queries.

You can temporarily stop the screen from repainting by using the Echo command.
Code:
Application.Echo False

DoCmd.OpenTable "ItemSorted"
DoCmd.Save acTable, "ItemSorted"
DoCmd.Close acTable, "ItemSorted"
................
................

Application.Echo True
But I think it will still have one repaint when you turn Echo back on.


Since your Alpha strings are of a fixed length, you can try the one-query approach. Maybe it is faster.

I have attached a query in the database. It will handle the ending E. If you need a table for the data source of the reports, you can change the query into a Make-table query.
 

Attachments

Last edited:
SQL Query with variable table

This new single Query code with the external variable table is very cool. I studied and copied over your new procedure into my database and it worked perfectly. You shrunk 19 queries down to 1 for me.

In total, it's actually down to 2 queries, One to delete all records and the second I call QuerySerial that Appends to the defined (but fairly temp.) table. They run OnOpen whenever I need Serial sort order.

----
Application.Echo False

CurrentDb.Execute "Query0"

DoCmd.OpenTable "ItemSorted"
DoCmd.Save acTable, "ItemSorted"

CurrentDb.Execute "QuerySerial"

DoCmd.Save acTable, "ItemSorted"
DoCmd.Close acTable, "ItemSorted"

Application.Echo True
----

Thanks Jon for all your help and working examples.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom