R
RNUSZ@OKDPS
I have an application that has 4 tables of data, they are:
Primary Table Record Layout for: Case-Record
------------------------------------------------------------------------
Field Field Primary Field
Name Length Key (Y/N) Type
=====================================
Case_Num_Yr 2 Y Number
Case_Num 4 Y Numeric
Tickle_Date mm/dd/yyyy N Date
PrtNo_Num S/Integer N Numeric
Prtd_Cde 1 N Y/N
Fr_File_Num 20 N Text
Typist_Init_Txt 3 N Text
Lic_Stat_Cde 4 N Text
Lic_DL_Stay_Cde 1 N Text
Result_Cde 6 N Text
Revo_Date mm/dd/yyyy N Date
Ofcr_Num 4 N Numeric
Acc_Date mm/dd/yyyy N Date
Cnty_Num 2 N Numeric
Req_Recd_Date mm/dd/yyyy N Date
Hrg_Date mm/dd/yyyy N Date
Hrg_Time 4 N Text
AM_PM_Txt 2 N Text
Loc_Cde 3 N Numeric
Atty_Num S/Integer N Numeric
Lic_First_Nme 30 N Text
Lic_Middle_Init 1 N Text
Lic_Last_Nme 30 N Text
Lic_Subt_Txt 5 N Text
Lic_Addr_Txt 30 N Text
Lic_City_Nme 30 N Text
Lic_State_Cde 2 N Text
Lic_Zip_Cde 5 N Text
Lic_Zip_Cde4 4 N Text
Lic_DL_Num 12 N Text
Security_Amnt S/Integer N Numeric
DOA_Nme 30 N Text
DOA_Addr_Txt 30 N Text
DOA_City_Nme 30 N Text
DOA_State_Cde 2 N Text
DOA_Zip_Cde 5 N Text
DOA_Zip_Cde4 4 N Text
Flag_Cde 10 N Text
Batch_Date mm/dd/yyyy N Date
Batch_Num S/Integer N Number
Memo1_Txt Memo N Memo
Memo2_Txt Memo N Memo
Memo3_Txt Memo N Memo
----------------------------------------------------------------
Secondary Table Record Layout: Case-Others
----------------------------------------------------------------
Field Field Primary Field
Name Length Key (Y/N) Type
=================================
Case_Num_Yr 4 Pri/pt1 Numeric
Case_Num 4 Pri/pt2 Numeric
Seq_Num 3 Pri/pt3 Numeric
Vehicle_Cde 2 Pri/pt4 Text
Other_Cde 2 N Text
Other_Nme 65 N Text
Firm_Nme 50 N Text
Other_Addr_Txt 30 N Text
Other_City_Nme 30 N Text
Other_State_Cde 2 N Text
Other_Zip_Cde 5 N Text
Other_Zip_Cde4 4 N Text
Updated_Date 8 N Date
---------------------------------------------------------------
Secondary Table Record Layout: Attorney
--------------------------------------------------------------
Field Field Primary Field
Name Length Key (Y/N) Type
===============================
Atty_Num Y Autonumb
First_Nme 20 N Text
Middle_Nme 15 N Text
Last_Nme 30 N Text
SubTitle_Txt 5 N Text
Firm_Nme 50 N Text
Addr1_Txt 30 N Text
Addr2_Txt 30 N Text
City_Nme 30 N Text
State_Cde 2 N Text
Zip_Cde 5 N Text
Zip_Cde4 4 N Text
Telephone_Num 14 N Text
TollFree_Num 14 N Text
FaxLine_Num 14 N Text
=================================
There will only be (1) one Case-Record Table record for each case on file,
key is combined fields [CASE_NUM_YR] and [CASE_NUM] 20041.
There could be (1) one or more secondary table records in the Case-Other
table, key is combined fields of [CASE_NUM_YR] , [CASE_NUM], [SEQ_NUM], and
[VEHICLE_CDE], there is also a possiblity that there could be "NO SECONDARY
TABLE Records for a Case". Table records are linked via [CASE_NUM_YR] and
[CASE_NUM].
A third table, ATTORNEY, will only have 1 record per attorney name, but
could be linked to CASE_RECORDS table records via CASE_RECORDS [ATTY_NUM]
field, which is the primary Key for the Attorney table.
I need to be able to read the CASE_RECORDS table, checking for value of 20
in PRTNO_NUM field, if found, match the existing CASE_OTHER RECORDS with
CASE_RECORD table records with value code of 20, along with accessing
attorney name table for CASE_RECORDS table records that have an existing
attorney number in the ATTY_NUM field. This would be needed to create both
printed mailing envelopes and mailing labels and still be able to keep the
corresponding names of people in the case together sequentially printed on
envelopes or labels.
There would be a need to capture from CASE_RECORDS Table the following
fields to create a mailing envelope & label:
(for envelope/label 1- fields needed are)
LIC_FIRST_NME
LIC_MIDDLE_NME
LIC_LAST_NME
LIC_SUBT_TXT
LIC_ADDR_TXT
LIC_CITY_NME
LIC_STATE_CDE
LIC_ZIP_CDE
LIC_ZIP4_CDE
(for envelope/label 2 - fields needed are) (from CASE_RECORDS Table)
DOA_NME
DOA_ADDR_TXT
DOA_CITY_NME
DOA_STATE_CDE
DOA_ZIP_CDE
DOA_ZIP4_CDE
for envelope / label next - fields needed are) from CASE_OTHERS table
OTHER_NME
FIRM_NME
OTHER_ADDR_TXT
OTHER_CITY_NME
OTHER_STATE_CDE
OTHER_ZIP_CDE
OTHER_ZIP4_CDE
and finally, if ATTY_NUM has a value in CASE_RECORDS, grab the corresponding
Attorney record fields:
FIRST_NME
MIDDLE_NME
LAST_NME
SUBTITLE_TXT
FIRM_NME
ADDR1_TXT
ADDR2_TXT
CITY_NME
STATE_CDE
ZIP_CDE
ZIP4_CDE
and use this data to create input to a envelope report / mailing label
report layout.
I believe its going to be pretty complex, and loaded with code, but was
wanting to know the best way to build/produce input. (via cross-table joined
queries ? or what would be the best way)..... Query the data building
separate query results table, then use that for input ?
I'm sure this has been done before, but would appreciate input to design to
make this as simple as it could be..
Thanks in advance.
Robert Nusz
Primary Table Record Layout for: Case-Record
------------------------------------------------------------------------
Field Field Primary Field
Name Length Key (Y/N) Type
=====================================
Case_Num_Yr 2 Y Number
Case_Num 4 Y Numeric
Tickle_Date mm/dd/yyyy N Date
PrtNo_Num S/Integer N Numeric
Prtd_Cde 1 N Y/N
Fr_File_Num 20 N Text
Typist_Init_Txt 3 N Text
Lic_Stat_Cde 4 N Text
Lic_DL_Stay_Cde 1 N Text
Result_Cde 6 N Text
Revo_Date mm/dd/yyyy N Date
Ofcr_Num 4 N Numeric
Acc_Date mm/dd/yyyy N Date
Cnty_Num 2 N Numeric
Req_Recd_Date mm/dd/yyyy N Date
Hrg_Date mm/dd/yyyy N Date
Hrg_Time 4 N Text
AM_PM_Txt 2 N Text
Loc_Cde 3 N Numeric
Atty_Num S/Integer N Numeric
Lic_First_Nme 30 N Text
Lic_Middle_Init 1 N Text
Lic_Last_Nme 30 N Text
Lic_Subt_Txt 5 N Text
Lic_Addr_Txt 30 N Text
Lic_City_Nme 30 N Text
Lic_State_Cde 2 N Text
Lic_Zip_Cde 5 N Text
Lic_Zip_Cde4 4 N Text
Lic_DL_Num 12 N Text
Security_Amnt S/Integer N Numeric
DOA_Nme 30 N Text
DOA_Addr_Txt 30 N Text
DOA_City_Nme 30 N Text
DOA_State_Cde 2 N Text
DOA_Zip_Cde 5 N Text
DOA_Zip_Cde4 4 N Text
Flag_Cde 10 N Text
Batch_Date mm/dd/yyyy N Date
Batch_Num S/Integer N Number
Memo1_Txt Memo N Memo
Memo2_Txt Memo N Memo
Memo3_Txt Memo N Memo
----------------------------------------------------------------
Secondary Table Record Layout: Case-Others
----------------------------------------------------------------
Field Field Primary Field
Name Length Key (Y/N) Type
=================================
Case_Num_Yr 4 Pri/pt1 Numeric
Case_Num 4 Pri/pt2 Numeric
Seq_Num 3 Pri/pt3 Numeric
Vehicle_Cde 2 Pri/pt4 Text
Other_Cde 2 N Text
Other_Nme 65 N Text
Firm_Nme 50 N Text
Other_Addr_Txt 30 N Text
Other_City_Nme 30 N Text
Other_State_Cde 2 N Text
Other_Zip_Cde 5 N Text
Other_Zip_Cde4 4 N Text
Updated_Date 8 N Date
---------------------------------------------------------------
Secondary Table Record Layout: Attorney
--------------------------------------------------------------
Field Field Primary Field
Name Length Key (Y/N) Type
===============================
Atty_Num Y Autonumb
First_Nme 20 N Text
Middle_Nme 15 N Text
Last_Nme 30 N Text
SubTitle_Txt 5 N Text
Firm_Nme 50 N Text
Addr1_Txt 30 N Text
Addr2_Txt 30 N Text
City_Nme 30 N Text
State_Cde 2 N Text
Zip_Cde 5 N Text
Zip_Cde4 4 N Text
Telephone_Num 14 N Text
TollFree_Num 14 N Text
FaxLine_Num 14 N Text
=================================
There will only be (1) one Case-Record Table record for each case on file,
key is combined fields [CASE_NUM_YR] and [CASE_NUM] 20041.
There could be (1) one or more secondary table records in the Case-Other
table, key is combined fields of [CASE_NUM_YR] , [CASE_NUM], [SEQ_NUM], and
[VEHICLE_CDE], there is also a possiblity that there could be "NO SECONDARY
TABLE Records for a Case". Table records are linked via [CASE_NUM_YR] and
[CASE_NUM].
A third table, ATTORNEY, will only have 1 record per attorney name, but
could be linked to CASE_RECORDS table records via CASE_RECORDS [ATTY_NUM]
field, which is the primary Key for the Attorney table.
I need to be able to read the CASE_RECORDS table, checking for value of 20
in PRTNO_NUM field, if found, match the existing CASE_OTHER RECORDS with
CASE_RECORD table records with value code of 20, along with accessing
attorney name table for CASE_RECORDS table records that have an existing
attorney number in the ATTY_NUM field. This would be needed to create both
printed mailing envelopes and mailing labels and still be able to keep the
corresponding names of people in the case together sequentially printed on
envelopes or labels.
There would be a need to capture from CASE_RECORDS Table the following
fields to create a mailing envelope & label:
(for envelope/label 1- fields needed are)
LIC_FIRST_NME
LIC_MIDDLE_NME
LIC_LAST_NME
LIC_SUBT_TXT
LIC_ADDR_TXT
LIC_CITY_NME
LIC_STATE_CDE
LIC_ZIP_CDE
LIC_ZIP4_CDE
(for envelope/label 2 - fields needed are) (from CASE_RECORDS Table)
DOA_NME
DOA_ADDR_TXT
DOA_CITY_NME
DOA_STATE_CDE
DOA_ZIP_CDE
DOA_ZIP4_CDE
for envelope / label next - fields needed are) from CASE_OTHERS table
OTHER_NME
FIRM_NME
OTHER_ADDR_TXT
OTHER_CITY_NME
OTHER_STATE_CDE
OTHER_ZIP_CDE
OTHER_ZIP4_CDE
and finally, if ATTY_NUM has a value in CASE_RECORDS, grab the corresponding
Attorney record fields:
FIRST_NME
MIDDLE_NME
LAST_NME
SUBTITLE_TXT
FIRM_NME
ADDR1_TXT
ADDR2_TXT
CITY_NME
STATE_CDE
ZIP_CDE
ZIP4_CDE
and use this data to create input to a envelope report / mailing label
report layout.
I believe its going to be pretty complex, and loaded with code, but was
wanting to know the best way to build/produce input. (via cross-table joined
queries ? or what would be the best way)..... Query the data building
separate query results table, then use that for input ?
I'm sure this has been done before, but would appreciate input to design to
make this as simple as it could be..
Thanks in advance.
Robert Nusz