Sorting WBS numbers

[Guest]

Is it possible to sort 6-level WBS numbers in Access 2002 or 2003?
Ex: A.2.3.6.5.1
A.1.6.9.1.3

to
A.1.6.9.1.3
A.2.3.6.5.1

Thx.

 

[Guest]

Did you try it? If all levels contain the same number of characters it
should sort correctly. The problem is if level 2 is numbered 1 through 12
and there is no leading zero.

 

[John Vinson]

Is it possible to sort 6-level WBS numbers in Access 2002 or 2003?
Ex: A.2.3.6.5.1
A.1.6.9.1.3

to
A.1.6.9.1.3
A.2.3.6.5.1

Thx.

You would need to store the numbers in six separate fields; sort by
them individually; and concatenate them for display purposes.

The alternative would be to have some pretty snarky and inefficient
VBA code to generate a sortkey which could deal with .10. values
anywhere in the string (or even .100. if that ever comes up).

John W. Vinson[MVP]

 

[Guest]

I didn't actually show all the complexity.

Here is the range of WBS formats:
A.1
A.1.2
A.4.2.1
A.5.3.4.11
A.5.12.1.6.5

Sometimes a query appears to work and sometimes they don't.

So I have added the page number to the table and tried using it to sort the
records. In most cases this works because there is usually only one WBS on a
page.

I could sort the records using the ID field if I put every record in the
correct order but I am not the person controlling the values and someone else
may decide to add a new WBS element somewhere in the middle of the table.

John has an excellent solution.

So how does MS Project handle WBS values?

LDN

 

[Guest]

I would add one more field for sorting and parse it with Excel. No matter
how far along they are in developing the WBS you can set up your parsing and
add in the new additions by append and update queries.

Copy the WBS field to Excel - do the parse - copy both the parse and orignal
and paste special on another worksheet. Copy into an Access table. Build an
update query by joining the WBS fields and updating the sort field.

 

[Jamie Collins]

The alternative would be to have some pretty snarky and inefficient
VBA code to generate a sortkey which could deal with .10. values
anywhere in the string (or even .100. if that ever comes up).

Just for fun, I decided to try and write some SQL code to do the same
(probably still 'pretty snarky and inefficient') ... but I ran into a
problem. Can anyone who speaks SQL take a look at this for me?

Sub test1()
With CurrentProject.Connection
..Execute _
"CREATE TABLE WbsNumbers (" & _
"wbs_nbr VARCHAR(22) NOT NULL);"
..Execute _
"INSERT INTO WbsNumbers VALUES (" & _
"'A.5.12.1.6.5');"
..Execute _
"INSERT INTO WbsNumbers VALUES (" & _
"'A.5.3.4.11');"
..Execute _
"INSERT INTO WbsNumbers VALUES (" & _
"'A.4.2.1');"
..Execute _
"INSERT INTO WbsNumbers VALUES (" & _
"'A.1.2');"
..Execute _
"INSERT INTO WbsNumbers VALUES (" & _
"'A.1');"
..Execute _
"CREATE TABLE Sequence (" & _
"seq INTEGER NOT NULL);"
..Execute _
"INSERT INTO Sequence VALUES (1);"
..Execute _
"INSERT INTO Sequence VALUES (2);"
..Execute _
"INSERT INTO Sequence VALUES (3);"
..Execute _
"INSERT INTO Sequence VALUES (4);"
..Execute _
"INSERT INTO Sequence VALUES (5);"
..Execute _
"INSERT INTO Sequence VALUES (6);"
..Execute _
"INSERT INTO Sequence VALUES (7);"
..Execute _
"INSERT INTO Sequence VALUES (8);"
..Execute _
"INSERT INTO Sequence VALUES (9);"
..Execute _
"INSERT INTO Sequence VALUES (10);"
..Execute _
"INSERT INTO Sequence VALUES (11);"
..Execute _
"INSERT INTO Sequence VALUES (12);"
..Execute _
"INSERT INTO Sequence VALUES (13);"
..Execute _
"INSERT INTO Sequence VALUES (14);"
..Execute _
"INSERT INTO Sequence VALUES (15);"
..Execute _
"INSERT INTO Sequence VALUES (16);"
..Execute _
"INSERT INTO Sequence VALUES (17);"
..Execute _
"INSERT INTO Sequence VALUES (18);"
..Execute _
"INSERT INTO Sequence VALUES (19);"
..Execute _
"INSERT INTO Sequence VALUES (20);"
..Execute _
"INSERT INTO Sequence VALUES (21);"
..Execute _
"INSERT INTO Sequence VALUES (22);"
..Execute _
"CREATE VIEW WbsSeq AS " & _
"SELECT WbsNumbers.wbs_nbr, Sequence.seq " & _
"FROM WbsNumbers, Sequence " & _
"WHERE MID$(WbsNumbers.wbs_nbr, Sequence.seq, 1) = '.';"
..Execute _
"CREATE VIEW WbsSortKeyPos AS " & _
"SELECT T1.wbs_nbr, T1.seq AS pos, " & _
"(SELECT COUNT(*)" & _
"FROM WbsSeq AS T2 " & _
"WHERE T2.wbs_nbr = T1.wbs_nbr " & _
"AND T2.Seq <= T1.Seq" & _
") AS sort_key " & _
"FROM WbsSeq AS T1;"
End With
End Sub

So far so good, but when I try the following SQL I get an error 'No
value given for one or more required parameters':

Sub test2()
Dim rs As Object
Set rs = CurrentProject.Connection.Execute( _
"SELECT T1.wbs_nbr, T1.sort_key, " & _
"T1.pos AS pos1, " & _
"(SELECT MIN(T2.pos) " & _
"FROM WbsSortKeyPos AS T2 " & _
"WHERE T2.wbs_nbr = T1.wbs_nbr " & _
"AND T2.pos > T1.pos) AS pos2 " & _
"FROM WbsSortKeyPos AS T1")
End Sub

The problem is the T1.pos in the subquery. Change it to, say, T2.pos >
T2.pos and it runs without error.

No doubt it is a limitation of the Jet engine because it works OK in
SQL Server 2000 (just replace MID$ with SUBSTRING) but I can't see
what limit I'm hitting.

Any ideas?

Thanks,
Jamie.

--