H
Hydra
Is there a way to make an index field that is numbered as in legal paragraphs?
ie
A1a
A2a
A2b
B1a
B2b
B2c
B3a
B4a
C1a
Etc.?
ie
A1a
A2a
A2b
B1a
B2b
B2c
B3a
B4a
C1a
Etc.?
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.
V
vanderghast
You meant ordering? because indexing is possible if the field is not a MEMO,
or an OLE field, or anything similar.
Depends on the exact formulation. If it is always one letter, one digit, one
letter, a standard ordering should do.
If the three parts may differ, I suggest you use 3 columns, rather than one,
say chapter, division, and clause. You can then
ORDER BY chapter, division, clause
Vanderghast, Access MVP
or an OLE field, or anything similar.
Depends on the exact formulation. If it is always one letter, one digit, one
letter, a standard ordering should do.
If the three parts may differ, I suggest you use 3 columns, rather than one,
say chapter, division, and clause. You can then
ORDER BY chapter, division, clause
Vanderghast, Access MVP
H
Hydra
The format is AA-ZZ.1-99.aa-zz
The way this works is that I have three linked tables: Grandparent,parent,
and child. Each is one to many with referential integrity. The user can
downselect at each level to constrct a stream of related phrases, which then
produces a report.
The user may add phrases if the library is incomplete for his purposes.
therfore these tables are autonumbering.
The user might select
3.1.5
3.1.7
4.2.1
4.3.1
4.3.2
And produce a report numbered this way.
The report needs to be numbered, in this case:
A.1.a
A.1.b
B.1.a
B.2.a
B.2.b
Fun huh? This wasn't my idea.
I'm assumming I will have to do some kind of lookup table, processed after
the downselect.
Hydra
The way this works is that I have three linked tables: Grandparent,parent,
and child. Each is one to many with referential integrity. The user can
downselect at each level to constrct a stream of related phrases, which then
produces a report.
The user may add phrases if the library is incomplete for his purposes.
therfore these tables are autonumbering.
The user might select
3.1.5
3.1.7
4.2.1
4.3.1
4.3.2
And produce a report numbered this way.
The report needs to be numbered, in this case:
A.1.a
A.1.b
B.1.a
B.2.a
B.2.b
Fun huh? This wasn't my idea.
I'm assumming I will have to do some kind of lookup table, processed after
the downselect.
Hydra
V
vanderghast
If there is a dot between the parts, that may help, but I still don't see
how 4.3.2 may become B.2.b
Note that instead of entering 3.1.5, would it not be as easy to enter 3
[tab] 1 [tab] 5 in a form (where [tab] would move the cursor to the next
text edit control ) ? It is much easier to work with data which is already
'ready to use' than with data we have to 'spot the dots, then brake in parts
to get the individual parts'. Entering each part in its own control CAN
simplify greatly the required SQL statements you could have to use further
on. A database works well when the data is EASILY available, not when data
is strangely encoded with a schema that YOU know but that the database is
totally unaware of.
Vanderghast, Access MVP
how 4.3.2 may become B.2.b
Note that instead of entering 3.1.5, would it not be as easy to enter 3
[tab] 1 [tab] 5 in a form (where [tab] would move the cursor to the next
text edit control ) ? It is much easier to work with data which is already
'ready to use' than with data we have to 'spot the dots, then brake in parts
to get the individual parts'. Entering each part in its own control CAN
simplify greatly the required SQL statements you could have to use further
on. A database works well when the data is EASILY available, not when data
is strangely encoded with a schema that YOU know but that the database is
totally unaware of.
Vanderghast, Access MVP
J
John Spencer
I might try something like the following in a report. One problem is if you
need to go beyond single letters. In that case you might need to write a VBA
function to take the three values from the controls and generate the needed
string. I won't attempt that since I don't know if the next value after Z is
AA, Aa, a, or AB or even something else.
Group on Grandparent,parent, and sort by Child
Create group headers for GrandParent and parent
Add a control to Grandparent group header
Name: txtTopLevel
Control Source: =1
Running Sum: Over All
Add a control to Parent group header
Name: txtLevel2
Control Source: =1
Running Sum: Over Group
Add a control to the Child (detail?) level
Name: txtLevel3
Control Source: =1
Running Sum: Over Group
In the detail section add one more control
Name: txtParaID
Control Source: Chr(TxtTopLevel + 64) & "." & [txtlevel2] & "." &
CHr([txtLevel3 +64)
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
need to go beyond single letters. In that case you might need to write a VBA
function to take the three values from the controls and generate the needed
string. I won't attempt that since I don't know if the next value after Z is
AA, Aa, a, or AB or even something else.
Group on Grandparent,parent, and sort by Child
Create group headers for GrandParent and parent
Add a control to Grandparent group header
Name: txtTopLevel
Control Source: =1
Running Sum: Over All
Add a control to Parent group header
Name: txtLevel2
Control Source: =1
Running Sum: Over Group
Add a control to the Child (detail?) level
Name: txtLevel3
Control Source: =1
Running Sum: Over Group
In the detail section add one more control
Name: txtParaID
Control Source: Chr(TxtTopLevel + 64) & "." & [txtlevel2] & "." &
CHr([txtLevel3 +64)
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
H
Hydra
:If there is a dot between the parts, that may help, but I still don't see
how 4.3.2 may become B.2.b"
Four is the second choice after 3, so 3 becomes A and 4 becomes B.
Three is the second choice under headiing 4 (which is now B), the first one
being 2 (which is now 1), wich gets us to B.2.
b is the second choice under 4.3 (which is now B.2), the first one being
4.3.1 (which is now B.2.a), which means 4.3.2 (in this case only) becomes
B.2.b.
Your government at work.
I'm sure I can do this, but i have aheadache thinking about it, not to
mention nausea at the thought that someone would actually require it.
how 4.3.2 may become B.2.b"
Four is the second choice after 3, so 3 becomes A and 4 becomes B.
Three is the second choice under headiing 4 (which is now B), the first one
being 2 (which is now 1), wich gets us to B.2.
b is the second choice under 4.3 (which is now B.2), the first one being
4.3.1 (which is now B.2.a), which means 4.3.2 (in this case only) becomes
B.2.b.
Your government at work.
I'm sure I can do this, but i have aheadache thinking about it, not to
mention nausea at the thought that someone would actually require it.
vanderghast said:If there is a dot between the parts, that may help, but I still don't see
how 4.3.2 may become B.2.b
Note that instead of entering 3.1.5, would it not be as easy to enter 3
[tab] 1 [tab] 5 in a form (where [tab] would move the cursor to the next
text edit control ) ? It is much easier to work with data which is already
'ready to use' than with data we have to 'spot the dots, then brake in parts
to get the individual parts'. Entering each part in its own control CAN
simplify greatly the required SQL statements you could have to use further
on. A database works well when the data is EASILY available, not when data
is strangely encoded with a schema that YOU know but that the database is
totally unaware of.
Vanderghast, Access MVP
Hydra said:The format is AA-ZZ.1-99.aa-zz
The way this works is that I have three linked tables: Grandparent,parent,
and child. Each is one to many with referential integrity. The user can
downselect at each level to constrct a stream of related phrases, which
then
produces a report.
The user may add phrases if the library is incomplete for his purposes.
therfore these tables are autonumbering.
The user might select
3.1.5
3.1.7
4.2.1
4.3.1
4.3.2
And produce a report numbered this way.
The report needs to be numbered, in this case:
A.1.a
A.1.b
B.1.a
B.2.a
B.2.b
Fun huh? This wasn't my idea.
I'm assumming I will have to do some kind of lookup table, processed after
the downselect.
Hydra
H
Hydra
"Note that instead of entering 3.1.5, would it not be as easy to enter 3
[tab] 1 [tab] 5 in a form "
The 3.1.5 are key fields form the three tables. They are autonumbered and
not enetered. They keep the referential integrity between the three parts of
each "phrase", if you will.
The first fiield has about forty entries of which the user will typically
select five or ten, but could be much more.
The second fiield has from three to 25 or so entries for each selection al
leel one.
And the third field may have many detailed entries which amount to tasks to
be completed. Depending on requirements, the user selects which entries
ensure the requiremnts are met, and then you have a check list, that ensures
the major tasks at level 1 are completed to spec.
It is a checklist with downselects. but once selected the Key fields are not
relevant and the report needs to be renumbered as if it were a new set of
tables. And "renumbered" using the Alpha designators.
But the Subject matter expert may recognize that the existing list is
incomplete and add new tasks or subtasks to meet the (new) spec. hence the
autonumbering and referential integrity in the original tables.
[tab] 1 [tab] 5 in a form "
The 3.1.5 are key fields form the three tables. They are autonumbered and
not enetered. They keep the referential integrity between the three parts of
each "phrase", if you will.
The first fiield has about forty entries of which the user will typically
select five or ten, but could be much more.
The second fiield has from three to 25 or so entries for each selection al
leel one.
And the third field may have many detailed entries which amount to tasks to
be completed. Depending on requirements, the user selects which entries
ensure the requiremnts are met, and then you have a check list, that ensures
the major tasks at level 1 are completed to spec.
It is a checklist with downselects. but once selected the Key fields are not
relevant and the report needs to be renumbered as if it were a new set of
tables. And "renumbered" using the Alpha designators.
But the Subject matter expert may recognize that the existing list is
incomplete and add new tasks or subtasks to meet the (new) spec. hence the
autonumbering and referential integrity in the original tables.
vanderghast said:If there is a dot between the parts, that may help, but I still don't see
how 4.3.2 may become B.2.b
Note that instead of entering 3.1.5, would it not be as easy to enter 3
[tab] 1 [tab] 5 in a form (where [tab] would move the cursor to the next
text edit control ) ? It is much easier to work with data which is already
'ready to use' than with data we have to 'spot the dots, then brake in parts
to get the individual parts'. Entering each part in its own control CAN
simplify greatly the required SQL statements you could have to use further
on. A database works well when the data is EASILY available, not when data
is strangely encoded with a schema that YOU know but that the database is
totally unaware of.
Vanderghast, Access MVP
Hydra said:The format is AA-ZZ.1-99.aa-zz
The way this works is that I have three linked tables: Grandparent,parent,
and child. Each is one to many with referential integrity. The user can
downselect at each level to constrct a stream of related phrases, which
then
produces a report.
The user may add phrases if the library is incomplete for his purposes.
therfore these tables are autonumbering.
The user might select
3.1.5
3.1.7
4.2.1
4.3.1
4.3.2
And produce a report numbered this way.
The report needs to be numbered, in this case:
A.1.a
A.1.b
B.1.a
B.2.a
B.2.b
Fun huh? This wasn't my idea.
I'm assumming I will have to do some kind of lookup table, processed after
the downselect.
Hydra
H
Hydra
Well, i have a qurey that generates the report, so I can get the selected
values, and put them in a temporary table or an array. Then I need three
nested loops to count the number of entries at each level and group. so if
the first group has four elements then they become ABCD no matter what the
original elements are numbered. If the second group has four elements
associated with A then they become 1234 no matter what they were previously,
And if the third level has five lements associated with A.2, then they become
abcde regardless of what was before.
What I don't knowis how to
A: Get the key filds out of the original query and read them into another
table or array
B: figure out the three nested loops (Case Switch, Do while, For next...)
C: Rewrite the entries that came out of the original query. I think I need
to change that to a make table query first, and then work on that table, and
then print the report from the revised table, and finally trash the table.
values, and put them in a temporary table or an array. Then I need three
nested loops to count the number of entries at each level and group. so if
the first group has four elements then they become ABCD no matter what the
original elements are numbered. If the second group has four elements
associated with A then they become 1234 no matter what they were previously,
And if the third level has five lements associated with A.2, then they become
abcde regardless of what was before.
What I don't knowis how to
A: Get the key filds out of the original query and read them into another
table or array
B: figure out the three nested loops (Case Switch, Do while, For next...)
C: Rewrite the entries that came out of the original query. I think I need
to change that to a make table query first, and then work on that table, and
then print the report from the revised table, and finally trash the table.
John Spencer said:I might try something like the following in a report. One problem is if you
need to go beyond single letters. In that case you might need to write a VBA
function to take the three values from the controls and generate the needed
string. I won't attempt that since I don't know if the next value after Z is
AA, Aa, a, or AB or even something else.
Group on Grandparent,parent, and sort by Child
Create group headers for GrandParent and parent
Add a control to Grandparent group header
Name: txtTopLevel
Control Source: =1
Running Sum: Over All
Add a control to Parent group header
Name: txtLevel2
Control Source: =1
Running Sum: Over Group
Add a control to the Child (detail?) level
Name: txtLevel3
Control Source: =1
Running Sum: Over Group
In the detail section add one more control
Name: txtParaID
Control Source: Chr(TxtTopLevel + 64) & "." & [txtlevel2] & "." &
CHr([txtLevel3 +64)
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
.The format is AA-ZZ.1-99.aa-zz
The way this works is that I have three linked tables: Grandparent,parent,
and child. Each is one to many with referential integrity. The user can
downselect at each level to constrct a stream of related phrases, which then
produces a report.
The user may add phrases if the library is incomplete for his purposes.
therfore these tables are autonumbering.
The user might select
3.1.5
3.1.7
4.2.1
4.3.1
4.3.2
And produce a report numbered this way.
The report needs to be numbered, in this case:
A.1.a
A.1.b
B.1.a
B.2.a
B.2.b
Fun huh? This wasn't my idea.
I'm assumming I will have to do some kind of lookup table, processed after
the downselect.
Hydra