E
Elfae
List Members,
I have searched high and low for a sample for this, and I just can't
find any. Sorry for the length!
Background Information
The issue revolves around setting up a system-generated increase in
two of a three-part field - one numeric the other text. The Org
Number, Incremental Number, and Subcontractor Designation make up the
Audit Identification number(7) for queries and reports. There is a
hyphen between Org Number and Incremental Number. I've had to number
the columns as I can't fit the names on them - sorry.
1. Autonumbering showing repeated sequences
1 - Audits Table - PK=tblAuditsID - covers 1, 2 and 3 Is 1:M
tblContractor
2. Org Number field (text) This is chosen from a combo box from a
lookup table, there are 27 different Org Numbers
3. Incremental Number field (numeric)
4. Contractor Name field
Contractor Table PK=tblContractorID, FK is tblAudit IDTable
5. SubContractor Designation field
SubContractor Table - PK=tblSubcontractorID, FK=tblContractorID -
covers 5 & 6
6. SubContractor Name
7. Audit Identification Number - Final needed results
1 2 3
4 5 6 7
1 P1100 100 OASIS
Co P100-0100
2 P1200 2173 XYZ
Co P1200-2173
3 P1300 569 123
Co P1300-0569
3
A 456 Co P1300-0569A
4 P1400 1952 WEX
Co P1400-1952
4
A KELP Co. P1400-1952A
5 P1500 2173 ABC
Co P1500-2173
5
A DEF Co. P1500-2173A
5
B Peter Paul P1500-2173B
5
C Mary Smith P1500-2173C
6 P1550 59
Boxer P1550-0059
7 P1600 961 Davis
Max P1600-0961
8 P1700 1
Charlie's P1700-0001
9 P1800 5 Mary
Lamb P1800-0005
10 P1900 590
Nemo P1900-0950
11 P2000 232
Harry
P2000-0232
Numeric Autonumber
The value in the Increment field (third column) represents the last
number used in the old system. In the new database it would have to
start at the last number shown in that field plus an increment of one
for each of the Org Numbers (2),in which there is a new assignment.
The user would also have to have the ability to:
* Open up an existing record and be able to add additional
subcontractors and/or amendments that reside in other tables.
* Be able to pull up an exiting record to add additional information
from another input form.
The last four fields (4-7) in the table above are in different tables
and are shown here for illustration purposes. I repeated the Primary
Key Autonumber (1) to show you when we are on the same record. The
real database would not have the primary key repeated.
Text Autonumber
When the user gets to the part of the record to enter the
SubContractor names (6)in the Subcontractor Table (6) he/she will use
a combo box to add those subcontractors who are working for a
particular prime contractor (4) into the SubContractor Name (6)
field. The contract identifies all the applicable subcontractors.
After the first subcontractor is added to the Subcontractor Table, a
value of A-ZZ (5) needs to be placed in the SubDesignation (5) field
in the Subcontractor Table. (5) It represents the designation for
that subcontractor. As each additional subcontractor is added in the
SubContractor Name field the system needs to use the next available
letter up to and including ZZ if there are that many subcontractors
included in the contract.
Clear as mud? From the Audit table, I need to choose the Org number
(P1100), add a dash, add the Incremental number (4 byte, so leading
zero's) to it plus 1, and if there is a subcontractor add an A (A-ZZ),
another subcontractor add a B & so on.
Thank you for even reading this. If my chart didn't come in clearly
and you are interested in helping, let me know and I'll send you
another one. I am just not a programmer and really need help with
this. All of the tables have been normalized, and if you need to see
the data model just let me know. Thank You.
Nancy
Home at Last
(e-mail address removed)
I have searched high and low for a sample for this, and I just can't
find any. Sorry for the length!
Background Information
The issue revolves around setting up a system-generated increase in
two of a three-part field - one numeric the other text. The Org
Number, Incremental Number, and Subcontractor Designation make up the
Audit Identification number(7) for queries and reports. There is a
hyphen between Org Number and Incremental Number. I've had to number
the columns as I can't fit the names on them - sorry.
1. Autonumbering showing repeated sequences
1 - Audits Table - PK=tblAuditsID - covers 1, 2 and 3 Is 1:M
tblContractor
2. Org Number field (text) This is chosen from a combo box from a
lookup table, there are 27 different Org Numbers
3. Incremental Number field (numeric)
4. Contractor Name field
Contractor Table PK=tblContractorID, FK is tblAudit IDTable
5. SubContractor Designation field
SubContractor Table - PK=tblSubcontractorID, FK=tblContractorID -
covers 5 & 6
6. SubContractor Name
7. Audit Identification Number - Final needed results
1 2 3
4 5 6 7
1 P1100 100 OASIS
Co P100-0100
2 P1200 2173 XYZ
Co P1200-2173
3 P1300 569 123
Co P1300-0569
3
A 456 Co P1300-0569A
4 P1400 1952 WEX
Co P1400-1952
4
A KELP Co. P1400-1952A
5 P1500 2173 ABC
Co P1500-2173
5
A DEF Co. P1500-2173A
5
B Peter Paul P1500-2173B
5
C Mary Smith P1500-2173C
6 P1550 59
Boxer P1550-0059
7 P1600 961 Davis
Max P1600-0961
8 P1700 1
Charlie's P1700-0001
9 P1800 5 Mary
Lamb P1800-0005
10 P1900 590
Nemo P1900-0950
11 P2000 232
Harry
P2000-0232
Numeric Autonumber
The value in the Increment field (third column) represents the last
number used in the old system. In the new database it would have to
start at the last number shown in that field plus an increment of one
for each of the Org Numbers (2),in which there is a new assignment.
The user would also have to have the ability to:
* Open up an existing record and be able to add additional
subcontractors and/or amendments that reside in other tables.
* Be able to pull up an exiting record to add additional information
from another input form.
The last four fields (4-7) in the table above are in different tables
and are shown here for illustration purposes. I repeated the Primary
Key Autonumber (1) to show you when we are on the same record. The
real database would not have the primary key repeated.
Text Autonumber
When the user gets to the part of the record to enter the
SubContractor names (6)in the Subcontractor Table (6) he/she will use
a combo box to add those subcontractors who are working for a
particular prime contractor (4) into the SubContractor Name (6)
field. The contract identifies all the applicable subcontractors.
After the first subcontractor is added to the Subcontractor Table, a
value of A-ZZ (5) needs to be placed in the SubDesignation (5) field
in the Subcontractor Table. (5) It represents the designation for
that subcontractor. As each additional subcontractor is added in the
SubContractor Name field the system needs to use the next available
letter up to and including ZZ if there are that many subcontractors
included in the contract.
Clear as mud? From the Audit table, I need to choose the Org number
(P1100), add a dash, add the Incremental number (4 byte, so leading
zero's) to it plus 1, and if there is a subcontractor add an A (A-ZZ),
another subcontractor add a B & so on.
Thank you for even reading this. If my chart didn't come in clearly
and you are interested in helping, let me know and I'll send you
another one. I am just not a programmer and really need help with
this. All of the tables have been normalized, and if you need to see
the data model just let me know. Thank You.
Nancy
Home at Last
(e-mail address removed)