Sorting with letters

[awojciehowski]

I have a database where I label entries by the alphabet (A-Z). However, when I have more entries than the alphabet has letters I then double the letters (AA-ZZ) and so on.

I want to create a sorting code that will list all single letters then double letters, triple and so on.

I am somewhat of a novice so if anyone has some ideas I sure would appreciate it along with a bit of detail.

Thanks much!
Adam

[FishVal]

Hello, Adam.

Try something like the following:

SELECT * FROM [YourTable] ORDER BY Len([Label]), [Label];
 

[awojciehowski]

Sorry, I am working in MS Access and am not that familiar with VB Coding. Could you provide a bit more detail on how to complete your suggestion?

Thanks!

[FishVal]

Queries in Access using SQL View
Basic SQL Syntax for Access Queries

http://en.wikipedia.org/wiki/Select_(SQL)

What makes records to be ordered is what is written in ORDER BY clause of SQL expression:

... ORDER BY Len([Label]), [Label];
 

this tells database engine to sort records first by length of [Label] field, then by value of [Label] field.
I guess in query design view grid it looks like column "ExprN: Len([Label])" with ascending sorting order and "[Label]" column with ascending sorting order rightward to it.

[awojciehowski]

FishVal-

I appreciate the help but this does me no good. I have no idea what you are trying to explain, even though I have read through it a few times. I attempted to modify my query with sort example but all I got was a bunch of errors.

Anyone else wanna take a stab at helping out?

Thanks
Adam

[FishVal]

Ok, Adam.

Open your query, change View to SQL, post contents of query window.

[NeoPa]

A QueryDef (saved query in Access) has two different ways that its design can be shown in Access :

1. Design View.
2. SQL View.

When either is displayed, the other can be selected by using the View menu and selecting the relevant option. What this means is that when changes are made in either view, the changes are visible in both.

Because of this, we can get a pretty good idea of what your query is doing simply by looking at the SQL (that you can see and post here for us simply by using the SQL view). It also means that we can give you changes to the SQL that you will be able to put into the SQL view of the QueryDef, and see the intended changes in Design view.

[awojciehowski]

Thanks for the explanation...here is the SQL info that I was able to recover:

SELECT   Entrys.SPDCaseNumber,
         Entrys.Location,
         Entrys.LedgerNumber,
         Entrys.OfficerID,
         Entrys.ReceivedDate,
         Entrys.[EvidenceDisposed?],
         Entrys.EvidenceTypeID,
         [Entry Details].LedgerID,
         [Entry Details].EvidenceName,
         [Entry Details].Quantity,
         [Entry Details].Notes,
         [Entry Details].ItemNumber,
         Entrys.EvidenceTypeID,
         Entrys.[Warrant?],
         Entrys.CaseResolvedDate,
         Entrys.TobeDisposed
 
FROM     Entrys INNER JOIN [Entry Details]
  ON     Entrys.LedgerID = [Entry Details].LedgerID
 
ORDER BY Entrys.LedgerNumber,
         [Entry Details].ItemNumber

[NeoPa]

I'll try to look at this in more detail later, but for now I've just rearranged the code so that it's a little easier to read and digest.

[NeoPa]

@awojciehowski
Adam, can you say which field you're referring to here? The one where you store the alphabetic characters.

[awojciehowski]

It would be the Item Number field that I would want to be organized as A, B, C then AA, BB, CC then AAA, BBB, CCC and so on.

[NeoPa]

In that case add some spaces at the front then chop off the rightmost n characters. I worked to a potential maximum of 5 chars, but you know your data better than I.

The other change I made (setting and using the ALIASes) had no real effect, it simply makes it easier to read and work with when in SQL view.

SELECT   tE.SPDCaseNumber,
         tE.Location,
         tE.LedgerNumber,
         tE.OfficerID,
         tE.ReceivedDate,
         tE.[EvidenceDisposed?],
         tE.EvidenceTypeID,
         tED.LedgerID,
         tED.EvidenceName,
         tED.Quantity,
         tED.Notes,
         tED.ItemNumber,
         tE.EvidenceTypeID,
         tE.[Warrant?],
         tE.CaseResolvedDate,
         tE.TobeDisposed
 
FROM     Entrys AS tE INNER JOIN [Entry Details] AS tED
  ON     tE.LedgerID=tED.LedgerID
 
ORDER BY tE.LedgerNumber,
         Right('    ' & tED.ItemNumber,5)

[NeoPa]

You could also use Fish's technique (which I missed). Just replace lines #21 & #22 with :

ORDER BY tE.LedgerNumber,
         Len(tED.ItemNumber),
         tED.ItemNumber

This is actually a better, more flexible technique anyway. I'd go with this version for preference.

[awojciehowski]

NeoPa-

I copied and pasted what you suggested into my query...now when I view the record it still is not sorted correctly and when I click on a button to print the current record is comes up with an missing parameter value of "Entry Details.Evidence Name".

I am attaching a screen shot for you to understand what I am looking at.

[NeoPa]

That makes the problem very clear Adam. I'm afraid you haven't copied & pasted the code across correctly.

That prompt reflects something that doesn't exist in my SQL.

I suggest you try again. If it fails again, copy in the SQL from your database again exactly as you did before, and include it in your next post.

[awojciehowski]

Maybe this will help...I have multiple queries working on this form.

SELECT [Add New Inventory Entry and Inventory Details].LedgerNumber, [Add New Inventory Entry and Inventory Details].SPDCaseNumber, [Add New Inventory Entry and Inventory Details].OfficerID, [Add New Inventory Entry and Inventory Details].ReceivedDate, [Add New Inventory Entry and Inventory Details].Location, Entrys.Comment, [Add New Inventory Entry and Inventory Details].ItemNumber, [Add New Inventory Entry and Inventory Details].EvidenceName, [Add New Inventory Entry and Inventory Details].Quantity, [Add New Inventory Entry and Inventory Details].LedgerID, [Add New Inventory Entry and Inventory Details].Notes, [Add New Inventory Entry and Inventory Details].[EvidenceDisposed?], Entrys.DispoDate, Entrys.DispositionMethod, Entrys.DispoCaseNumber, Entrys.Keep
FROM Entrys INNER JOIN [Add New Inventory Entry and Inventory Details] ON Entrys.LedgerID = [Add New Inventory Entry and Inventory Details].LedgerID;

However, I understand that my mistake may be related to the "Print Current Record" button...what is the problem for the sub form of the list of Item #'s still not showing to be sorted correctly?

[NeoPa]

Well, what can I say?

This looks nothing like what I posted.

The techniques I used are not here and it's displayed as an enormous dump of characters. I've tidied it up once to work with, I don't want to do it again thank you.

I'm not sure what you want from me. This is something totally different from what I suggested. How can I help if you choose to ignore what I suggest?

I admit to being a little confused.

[awojciehowski]

NeoPa-

I am sorry to offend you...it appears that you are quite upset about my last post. I am not well versed in SQL or Access thus the posting for help. I was reviewing the queries that were associated with the form that I posted a screen shot of. I found that the "Print Current Form" had a seperate query and that is what I posted since the error as described above occurred when I clicked on the "Print Current Record" button.

I realize that you are a consumate contributor to this site and do apologize for my impotence regarding Access. However, if this is going to be resolving into a frustration for you and a curt response that is not needed then I thank you for your time and will drop the issue.

Anyone else willing to assist without the condensation?

[NeoPa]

To be honest Adam, I was more confused than upset. I'm equally confused that you should interpret my quite reasonable post as condescension.

I simply asked you to post back the SQL from the query I thought I was helping you with. This is not a task that requires any previous Access or SQL expertise. Your previous experience should have no bearing on this whatsoever.

I was at a loss (confused) and I had no idea how to respond when I saw your post. This seemed to be entirely unconnected with what I'd asked for. How does someone explain that you got it so wrong without sounding insulting - bearing in mind this was nothing to do with experience or lack thereof? You try. Maybe you could have found better words than I did.

I'm happy to respect your wishes though, whatever reason that may be for.

I can simply wish you good luck with your project.