If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Ideas On Producing Envelopes & Labels For Data
I have an application that has 4 tables of data, they a
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 -- Robert Nusz Sr. Programmer Analyst II |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Envelopes and Labels Problem | Stephen Glynn | New Users | 2 | May 26th, 2004 08:21 AM |
Envelopes and labels, delivery and return address. | Cassie | Mailmerge | 1 | May 13th, 2004 05:33 PM |
Printing of address labels | johnfairweather | Mailmerge | 2 | May 12th, 2004 11:59 PM |
REPOST:Can't use address book contacts for envelopes and labels | Tony Girgenti | New Users | 0 | May 12th, 2004 06:47 PM |
REPOST:Can't use address book contacts for envelopes and labels | Tony Girgenti | Mailmerge | 0 | May 12th, 2004 06:47 PM |