[AccessD] Insert record into fake group

I won't ask why so will this work for you? SchoolOrder is a text field.
 
Table1
SchoolID	 SchoolOrder	
1	 001.001	
4	 001.002	
6	 001.003	
5	 001.004	
2	 002.001	
8	 002.002	
10	 002.003	
12	 002.004	
14	 002.005	
15	 002.006	
18	 002.007	
20	 002.008	
21	 002.009	
23	 002.010	
25	 002.011	
3	 003.001	
9	 003.002	
11	 003.003	
13	 003.004	
16	 003.005	
17	 003.006	
19	 003.007	
22	 003.008	
24	 003.009	
26	 003.010	
31	 010.001	
32	 010.002	
33	 011.001	
35	 011.002	
36	 011.003	
34	 012.001	
37	 012.002	
 
 

John V. Ruff - The Eternal Optimist :-) 
Always Looking For Contract Opportunities 

Home: 253.588.2139 
Cell: 253.307/2947 
9306 Farwest Dr SW 
Lakewood, WA 98498 

"Commit to the Lord whatever you do, 
                and your plans will succeed." Proverbs 16:3 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Ervin Brindza
Sent: Thursday, May 08, 2003 3:38 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] Insert record into fake group



Cross posted
Hi,
I need a "little" advice about record order. The fields are:
"SchoolId"(Integer) and "SchoolOrder"(Text) which cause the problem. The
field "SchoolOrder" is text, not number, because there is a need to insert a
new school between two existing ones.
For example:
SchoolID SchoolOrder
1            1
2            2
3            3
and the customer want to insert the fourth school between the first and the
second(the new school is somehow related to the first school, creating a
"fake group" with the first) then the SchoolOrder is 10 and:
SchoolID SchoolOrder
1            1
4            10
2            2
3            3
The pair(5, 11) will be on the 3. place, the (6, 101) will be behind the
(4,10) etc. 
1            1
4            10
6            101
5            11
2            2
3            3
 
This is all right but what should I do when there are more than 9 "fake
groups".
I tried with:
SchoolID SchoolOrder
1            1/1
4            1/2
6            1/3
5            1/4
2            2/1
3            3/1
and in the SQL in the ORDER BY clause to put(separate the left and the right
side from the "/"):
Mid([tblSchool].[SchoolOrder],1,InStr([tblSchool].[SchoolOrder],"/")-1) &
Mid([tblSchool].[SchoolOrder],InStr([tblSchool].[SchoolOrder],"/")+1,Len([tb
lSchool].[SchoolOrder]));
I tried wrapping into CInt function but no success too.
I know that additional field to separate the field SchoolOrder parts would
solve my problem, but the customer diesn't like the idea to modify the table
structure.
Can anyone shed any light?
Many thanks in advance,
    Ervin

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030508/54adb915/attachment-0001.html>