how to create custom sort sql statement

[TM]

I have a table for a shopping list, with two fields: isle, and item.

I want to create a custom sort on the isle field that is a 5 character
alphanumeric field, but do not know how.

The field can contain a number, or an alpha and I want to take three alpha
fields and place them at the beginning of the table in this order:

"ENT" first
"PROD" second
"PHRM" third
any number from 1-16 next

I found a custom sort order string in an SQL server web page but can not
figure out how to do this in access or as a command string in my vb.net
application.

The custom search order goes like this:
SELECT *
FROM shoppinglist
ORDER BY
CASE isle
WHEN 'ENT' THEN 1
WHEN 'PROD' THEN 2
WHEN 'PHRM' THEN 3
ELSE 4
END

Any help is greatly appreciated.

 

[John Spencer (MVP)]

You can do this using a set of nested IIF statements.

....
ORDER BY IIF(Isle="ENT",1,IIF(Isle="Prod",2,IIF(Isle="PHRM",3,4)))

You might be better off adding another table with your sort order and then
joining that to your query.

TableName: IsleSortOrder
Field1: IsleID (text field)
Field2: SpecialSort (number field)

You would have 19 records in this table - one for each isle (aisle). Something like:
Ent 10
Prod 20
Phrm 30
1 40
2 40

Then add this table to your query and join IsleID to Isle in your query. You
can then sort by SpecialSort with ease and you can add/insert new aisles easily
in the table and never have to modify your Order by clause.

 

[TM]

I tried that and although it did put the ones I wanted at the top of the
list, it totally screwed up the rest of the entries in the recordset.

The other entries all contained a number, and it seemed that the sort was
totally random.

What I would like to see in the sort is something like this:
ENT
PROD
PHRM
1
2
3
4
......
16

I do not understand what you mean by adding another table. Can you please
clarify ?

 

[John Spencer (MVP)]

Ah, but you did not say you wanted the numbers sorted in order also. Just make
a minor change to add 3 to the Isle value

ORDER BY IIF(Isle="ENT",1,
IIF(Isle="Prod",2,
IIF(Isle="PHRM",3,3 + Val(Isle))))

That will all be on one line, but for clarity I showed this on multiple lines.

Can you add another table to your database? If so, you would follow my example
and then in the query you would end up with something like

SELECT YourTable.*
FROM YourTable INNER JOIN IsleSortOrder
ON YourTable.Isle = IsleSortOrder.IsleID
ORDER By IsleSortOrder.SpecialSort

If you don't know how to write SQL and are using the grid to build your queries, then
Add the table IsleSortOrder to the query
Click on the IsleField in yourtable and drag over to the IsleID in IsleSortOrder
Add the SpecialSort field to the field list
Click on Sort and click ascending.

If the above is still not clear, then I am stuck on how to explain it to you
without a one on one training session.

 

[TM]

Sorry about that. my bad.

I really do like your idea about using another joined table for the sorting.
I can add and configure tables no problem. But the whole sorting idea is a
little confusing.

Thank you for the clarification in your message today. I will give it a shot
and see what happens.

I think your table idea seems like the best solution.

 

[TM]

Awesome dude, you are a genius. I just typed in some quick code, created
the table as you mentioned and whohoo. It sorted just like it was supposed
to.

I haven't tried any record modifications such as adding/deleting or
whatever. But I can't see why it wouldn't work.

I will do some more work on it later and let you know what happens. It only
took a few minutes, not including fixing typo's of course, and works great.

I really appreciate the help

 

[TM]

Ok, I spoke to soon. I am confused as how to configure this properly so
that I can add records.

I may be totally confused as to datasets and such, but please bear with my
as the only experience I have is with a class that I just finished.

Here is my code to open the database and stuff:
Public objConnection As New OleDb.OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabaseFileName)

Public objShoppingListDA As New OleDb.OleDbDataAdapter("SELECT * FROM
ShoppingList" & _
" INNER JOIN IsleSortOrder ON ShoppingList.Isle =
IsleSortOrder.IsleID ORDER By
IsleSortOrder.SpecialSort", objConnection)
Public objShoppingListCB As New OleDb.OleDbCommandBuilder(objShoppingListDA)
Public objShoppingListDataSet As New DataSet

And to bind that to my grid I am using:
'clear dataset
objShoppingListDataSet.Clear()
objShoppingListDA.FillSchema(objShoppingListDataSet, SchemaType.Source,
"ShoppingList")
'fill dataset with info from dataAdapter
objShoppingListDA.Fill(objShoppingListDataSet, "ShoppingList")
grdShoppingList.DataSource = objShoppingListDataSet
grdShoppingList.DataMember = "ShoppingList"

But this give me an error if I try to update the database:
Dynamic SQL generation is not supported against multiple base tables.

Also, is there any way I can hide the additional fields from the sprtid
table from the grid ? All I would like to see in my datagrid is the Isle
and Item from the shopping list.

Thanks

 

[John Spencer (MVP)]

Ok new territory for me.

First, change the query to
"SELECT ShoppingList.Isle, ShoppingList.Item FROM ShoppingList ..."
That should take care of you showing the fields in the other table.

As for the other problems, I've got no idea. Perhaps someone else can step in
to help you. Or you can post this as a new problem.

 

[TM]

Well that seems to have taken care of everything. No errors. Although I
seem to have another problem, not related to this one.

Are you familiar with the datagrid ?

I wanted to detect any changes to the grid, such as a record being changed,
added or deleted. So I used the currentcellchanged method and used the code
below to update the database. But for some reason, it is detecting and
correctly updating the database on add and remove of records. But is not
writing cell changes back to the database. ANy ideas ?

'this sub will handle if a cell is changed or if a new record is added
Dim dgCell As DataGridCell = grdShoppingList.CurrentCell
Dim strIsle As String = grdShoppingList.Item(dgCell.RowNumber, 0).ToString
Dim strItem As String = grdShoppingList.Item(dgCell.RowNumber, 1).ToString
If strItem.Trim = "0" And strItem.Trim.Length <> 0 Then
'do not add record if it is blank
'write updates to database file and set label to number of records
objShoppingListDA.Update(objShoppingListDataSet, "ShoppingList")
updateShoppingLabel()
End If



ALso, is there any way to force a re-sort of the grid when I add or delete
records so that it is now sorted in the proper order ?

Thanks and sorry if I am being a pest

 

[John Spencer (MVP)]

Sorry, no. I've never used the datagrid. I suspect that your are talking about
something that is outside of Access as the only datagrid that I've heard of has
been an add-on of some type.

Try terminating this thread and starting a new thread. Also try to give a bit
more detail on the datagrid and your setup. Such as what version of Access, the
name of the datagrid product (if applicable), etc.

 

[TM]

Actually I should of said that the datagrid is in Visual Basic .net.

But I will post a new message on that. Thanks

 

[TM]

I have another question if you don't mind. There sees to be a problem with
hit method if the user enters a new record, but there is no associated
record for the new isle entered in the IsleSOrt order table.

For example, I have in the islesort order table:
ENT, PROD, PHRM, and numbers 00-16

AN unhandled exception of type "system.data.oledb.oledbexception: occured in
system.data.dll

The exception desctiption is as follows:
But if they enter anyhting else, for a new record I get an exception error
like this:
The changes you requested to the table were not successful because they
would create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries nd try again.

What I did was try and enter a new record, and typoed the isle, and entered
ENN instead of ENT, and it blew up. If I enter a new isle that contains a
reference in the islesort table it works fine. BUt if I enter an isle that
does not contain a refernece in the islesort table it blows up.

 

[TM]

Ok, I think I found out part of the problem.

It is actually allowing me to enter a new record if there is no associated
isle in the islesort table. BUt, when I look at the database using the
custom join you gave me, it only returns records that have an isle entry in
the islesort table.

So the record is allowed to be entered but just does not show with the
select statement.

A refresher:
TableName: IsleSortOrder
Field1: IsleID (text field)
Field2: SpecialSort (number field)
isleid contains ENT, PROD, PHRM, and numbers 00-16

select statement:
"SELECT ShoppingList.Isle, ShoppingList.Item FROM ShoppingList INNER JOIN
IsleSortOrder
ON ShoppingList.Isle = IsleSortOrder.IsleID ORDER By
IsleSortOrder.SpecialSort"

I entered a new record with isle as ENN instead of ENT by mistake. It did
not show in the datagrid. But if I open the access file in access, the
record with the wrong isle is in there.

So when I did it again later by mistake the second item, it displays that
error.

Any idea how to fix this ?

Thanks