423,850 Members | 1,562 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,850 IT Pros & Developers. It's quick & easy.

How To Sort Records Alphabetically BUT Exclude Just One Record

nicebasic
P: 91
I would like to have a query for ASP Classic to be applied on an Access database.

1. Imagine that we have a table with the name of "Country".
2. Imagine that we have a column in "Country" that has this name: "CityMajor"
3. Imagine that we wish to have a query sort the items in this column, but with one exception: One of the records should stand on top of the other records while other records are sorted alphabetically.
4. An example could clarify this point. For example, we have the list of the following Major Cities in our list (Unsorted Alphabetically):

Paris
London
Tehran
Rome
New York
Berlin
Tokyo

5. Now we wish to have this list returned and sorted alphabetically, but with the exception that "New York" stands on top of the other cities. But the other items should be in alphabetical order:

New York
Berline
London
Paris
Rome
Tehran
Tokyo

How can I accomplish this query in ASP Classic for an Access Database?

Thank you in advance for any help in this regard.
Sep 20 '10 #1

✓ answered by ADezii

To make things clearer for you, I created a Demo that populates a List Box with the desired results. There is no reason why the code should not work, unless you are using an earlier Version of Access, in which case there is a work-a-round. Download the Attachment.

Share this Question
Share on Google+
20 Replies


ADezii
Expert 5K+
P: 8,591
  1. If you wish to simply generate a List, then pass to the following Function the Name of the City you want at the Top of the List. All other Cities will follow alphabetically. For the sake of brevity and simplicity, the Code does not include Error Checking, makes the major assumption that the [City Major] Field is REQUIRED (cannot be NULL), and only minimal Validation is performed as regards the String passed to the Function.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fSpecialSort(strTop As String)
    2. Dim MyDB As DAO.Database
    3. Dim rstSort As DAO.Recordset
    4. Dim strSQL As String
    5.  
    6. 'See if we have actual Records
    7. If DCount("[CityMajor]", "Country") = 0 Then Exit Function
    8.  
    9. 'Is strTop a City contained in the [CityMajor] Field?
    10. If DCount("*", "Country", "[CityMajor] = '" & strTop & "'") = 0 Then
    11.   MsgBox strTop & " does not exist in the Country Table!", vbExclamation, "Invalid City Name"
    12.     Exit Function
    13. End If
    14.  
    15. strSQL = "SELECT [CityMajor] FROM Country ORDER BY [CityMajor];"
    16.  
    17. Set MyDB = CurrentDb
    18. Set rstSort = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    19.  
    20. Debug.Print strTop      'PRINT City to be at the Top of List
    21.  
    22. With rstSort
    23.   Do While Not .EOF
    24.     If ![CityMajor] <> strTop Then      'EXCLUDE City at the Top, do not repeat
    25.       Debug.Print ![CityMajor]
    26.     End If
    27.       .MoveNext
    28.   Loop
    29. End With
    30.  
    31. rstSort.Close
    32. Set rstSort = Nothing
    33. End Function
    34.  
  2. Sample Function Call ('New York' on Top)
    Expand|Select|Wrap|Line Numbers
    1. Call fSpecialSort("New York")
    Sample OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. New York
    2. Berlin
    3. London
    4. Paris
    5. Rome
    6. Tehran
    7. Tokyo
Sep 20 '10 #2

nicebasic
P: 91
Thank you for your clean code.
Since I'm not as professional as you, I find your code rather difficult. I pasted your code in a New VB Form, but it stopped on this line:

Expand|Select|Wrap|Line Numbers
  1. If DCount("CityMajor", "Country") = 0 Then Exit Function
The Function "DCount" was not known to the program and was highlighted by the IDE. The error code was as follows:

Expand|Select|Wrap|Line Numbers
  1. Sub or Function not defined.
I added a Reference to "Microsoft DAO 3.6 Object Library", but it didn't help.

How can I run your code in a project FROM SCRATCH ?

By the way, I'm going to run this solution for an ASP Classic program. It might be a little different to apply VB SQL queries to ASP Classic code because of minor syntax difference.

Your solution, seems to be a really good one. But, isn't there an SQL command to help us more about this. I mean, if we have to loop through all the records and EXCLUDE one record, it might be rather slow for large Databases and might occupy system resources. As the code is to be run on the Web, would it be possible for you to help me find an SQL Query for this?

Thank you very much for you kind help and support.
Sep 21 '10 #3

100+
P: 255
DCount is part of the function contained in Access, so it should be working. Are you actually running the code that is not in the Access VBA environment?
Sep 21 '10 #4

nicebasic
P: 91
I tested your code in Visual Basic 6.0 IDE (and not in the Access VBA IDE) and tried to run it. As you mentioned, the DCount Function does not work in the Visual Basic 6.0 Environment.

I haven't used VBA in Access up to now.

I'll give it a try, though it's rather difficult for me. I'm not used to it.

Thank you very much for your kind attention.
Sep 21 '10 #5

Delerna
Expert 100+
P: 1,134
Sorry for butting in but when I need to do this I generally arrange my query so it adds a calculated field for sorting by "sort classes", as I like call it. So the query would return something like this
Expand|Select|Wrap|Line Numbers
  1. SrtClass   City
  2. 2          Paris
  3. 2          London
  4. 2          Tehran
  5. 2          Rome
  6. 1          New York
  7. 2          Berlin
  8. 2          Tokyo
  9.  
Now all I would do is
Expand|Select|Wrap|Line Numbers
  1. SELECT City
  2. FROM TheQueryWithTheSrtClass
  3. ORDER BY SrtClass,City
  4.  
in the query for my display of the cities
Sep 21 '10 #6

nicebasic
P: 91
You talked of "Sort Classes". Maybe you look at me as a professional. I'm so new to SQL Queries and just know some easy SQL Statement. You seem to be a pro. I don't understand the two phrases (calculated field) & (sort class):

calculated field for sorting by "sort classes"

I have already made use of a kind of "Sort Field" to solve this problem. But, I've got another Database that's fixed and not mine. I have to change the code that accesses that database. I can't add anything to the specified database. That's why, I have to modify my code.

By the way, is it possible to have "calculated field" and "sort classes"? Are they some sort of "Temporary Fields" that are created on the fly and can be removed easily?

Thank you very much.
Sep 21 '10 #7

ADezii
Expert 5K+
P: 8,591
To make things clearer for you, I created a Demo that populates a List Box with the desired results. There is no reason why the code should not work, unless you are using an earlier Version of Access, in which case there is a work-a-round. Download the Attachment.
Attached Files
File Type: zip Sort.zip (16.6 KB, 66 views)
Sep 21 '10 #8

nicebasic
P: 91
Thank you very much, ADezii.

This is the best thing you did. I needed a DEMO, since I haven't done VBA programming in Acesss. You're great.

Thank you a million times.
I really appreciate your help.
Sep 21 '10 #9

Delerna
Expert 100+
P: 1,134
Just to clarify for you the meaning of

(calculated field) & (sort class):


Sort class is a phrase of my own invention.
I am referring to the numbers in the sample data. When the data is sorted it will sort all the 1's first and then all the 2's last
So I effectively have a way of splitting your city data into 2 (or more) groups for sorting purposes



A calculated field is just a field you create within a query. It does not exist in any table.
So the Cls field in my posted data is a calculated field and would possible look something like for your sample
I am being simple in this example

Expand|Select|Wrap|Line Numbers
  1. SELECT iif([city]="New York",1,2) as Cls,
  2.     City
  3. FROM theTable
  4.  
  5.  

another example of a calculated field as, say a record for a customer order.
The customer buys a certain quantity of an item for a certain unit cost.

In the table you would have a Qty and a Cost field. But you wouldn't have a total field because you would calculate that in the query used by any forms or reports.

So, total in this query is a calculated field, it dosn't exist in the orders table
Expand|Select|Wrap|Line Numbers
  1. SELECT ItemNo,Qty,Cost,Qty*Cost as Total
  2. FROM tblOrders
  3.  
Sep 21 '10 #10

ADezii
Expert 5K+
P: 8,591
You are quite welcome, but I actually think that Delerma's solution in Post #6 is much more efficient and definitely simpler. The choice, of course, is yours.
Sep 21 '10 #11

ADezii
Expert 5K+
P: 8,591
You are quite welcome, but I actually think that Delerma's solution in Post #6 is much more efficient, practical, and definitely simpler, as long as you do not mind periodically changing Values (2) in the [SrtClass] Field. The choice, of course, is yours.
Sep 21 '10 #12

ADezii
Expert 5K+
P: 8,591
You are quite welcome, but I actually think that Delerna's solution in Post #6 is much more efficient, practical, and definitely simpler, as long as you do not mind periodically changing Values (2) in the [SrtClass] Field. The choice, of course, is yours.
Sep 21 '10 #13

ADezii
Expert 5K+
P: 8,591
Sorry for all the Dups, not sure what exactly happened.
Sep 21 '10 #14

Delerna
Expert 100+
P: 1,134
Yes as ADezii highlights, this method cannot always work
Expand|Select|Wrap|Line Numbers
  1. SELECT iif([city]="New York",1,2) as Cls, 
  2.     City 
  3. FROM theTable 
  4.  
the above code is meant for illustration purposes. In practice you need somthing more elaborate than that if you need to dynamically allow different cities to be at the top


Perhaps
Expand|Select|Wrap|Line Numbers
  1. SELECT iif([city]=Forms!FormName.txtTopCity,1,2) as Cls, 
  2.     City 
  3. FROM theTable 
  4.  
as a slightly better example
Sep 22 '10 #15

nicebasic
P: 91
Thank you very much, Delerna.

You've been so kind in this case. I thank you so much for being so informative.

Actually, you seem to be a really professional programmer. Your method is a brilliant and systematic one. There is a problem with me, however. I'm not as knowledgeable as you are in this case.

I wonder if it's possible to apply your commands to an ASP Classic code. Since the syntax of SQL Queries in ASP Classic is different from Access VBA, I'll have to focus on this matter more.

Thank you very much indeed for your kind attention.

I warmly appreciate your help, Delerna.
Sep 22 '10 #16

ADezii
Expert 5K+
P: 8,591
I took the liberty of creating a 2nd Demo based solely on Delerna's approach, which again is more efficient. The Method, I feel is essentially foolproof in that:
  1. The Combo Box will only display 'Unique' Cities within the Country Table.
  2. Select a City from the Combo Box, then click on the Command Button to Open a Query based on Delerna's Logic. There need be no Validation on the City Name with this approach.
  3. Should the Combo Box not contain a Value (NULL) when the Command Button is clicked, the User is warned of this via a Message Box, focus is now set to the Combo Box, and it is Dropped Down for another try.
  4. Download Sort2.zip and you'll see exactly what I mean.
@Delerna - I hope you did not mind that I used your Code. It's simply that yours is a much better approach, and I would prefer to see it used in this case. I am definitely not trying to 'Step on Your Toes'. If you feel as though my adaptation can be further improved, please feel free to do so. Thanks.
Attached Files
File Type: zip Sort2.zip (19.0 KB, 60 views)
Sep 22 '10 #17

Delerna
Expert 100+
P: 1,134
Adezii
I have no problems with that at all.

I come here to share what I have learned as well as to learn from what others share
Sep 22 '10 #18

Delerna
Expert 100+
P: 1,134
Nicebasic

I wonder if it's possible to apply your commands to an ASP Classic code. Since the syntax of SQL Queries in ASP Classic is different from Access VBA, I'll have to focus on this matter more.
Classic asp is starting to become "old school" but the answer is yes. I still use classic asp connecting to MSSQL server (access is not much different) and querying for data to display on intranet web pages for almost everything I do.
What, exactly, do you need to know? I will help if I can
Sep 22 '10 #19

nicebasic
P: 91
This is really great, ADezii.

You made a great demo in your excellent Sort2 project.

Thank you again and again for being so kind.
Sep 23 '10 #20

nicebasic
P: 91
I have learnt a lot thanks to ADezii's and YOUR contribution to THIS POST.

I still cannot apply this code to my ASP Classic project. Since you're familiar with ASP Syntax, I wonder if you can help me accomplish this in ASP Classic.

To make this question more relevant, I have posted this question in the ASP Classic Section as well. The link to the ASP Classic Question Page is as follows:

Please Visit This Page!


I appreciate any kind of help you might have for me. You seem to have a special method or technique of your own that is unique.

I hope I can use your sophisticated method in my ASP Source Code.

Thank you again for being so helpful and informative.

Good luck, Delerna.
Sep 23 '10 #21

Post your reply

Sign in to post your reply or Sign up for a free account.