R
roccogrand
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.
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 are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
KARL DEWEY
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.
should sort correctly. The problem is if level 2 is numbered 1 through 12
and there is no leading zero.
J
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]
R
roccogrand
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
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
K
KARL DEWEY
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.
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.
C
Chaim
Is this sort of problem amenable to a nested sets rep and solution? WBS is
after all a tree.
after all a tree.
R
roccogrand
Thanks all.
I solved the problem by adding a new field to the table that holes the page
number on which the WBS element is found in the source document. This way,
users enter the page numbers and a query sorts the records with WBS elements
logically. When more than one WBS number appear on a page, users can add a
fraction to the sort value.
This taught me that WBS numbers create sorting problems for databases
because they are treated as regular text fields. I wonder how MS Project
handles them but if I remember correctly Project also has a problem with
them. Hopefully in Access 12 there will be a new data field called "WBS"
that will handle this useful type of information.
L
I solved the problem by adding a new field to the table that holes the page
number on which the WBS element is found in the source document. This way,
users enter the page numbers and a query sorts the records with WBS elements
logically. When more than one WBS number appear on a page, users can add a
fraction to the sort value.
This taught me that WBS numbers create sorting problems for databases
because they are treated as regular text fields. I wonder how MS Project
handles them but if I remember correctly Project also has a problem with
them. Hopefully in Access 12 there will be a new data field called "WBS"
that will handle this useful type of information.
L