I have 2 tables in a database called “AccountActivity” & “ReqLog” respectively.
The “AccountActivity” table has 9 columns.
Columns A = “Account” is Numeric
Columns B = “BudgetCategory” is Numeric
Columns C = “Voucher Nbr” is Text
Columns D = “REF Type” is Text
Columns E = “Encumbrance Nbr” is Numeric
Columns F = “Prefix” is Text
Columns G = “RequisitionNbr” is Numeric
Columns H = “Trans Date” is Date/Time
Columns I = “Obj Code Class” is Numeric
The “ReqLog” table has 6 columns.
Columns A = “AutoID” is AutoNumber
Columns B = “Account” is Numeric
Columns C = “Prefix” is Text
Columns D = “RequisitionNbr” is Numeric
Columns E = “Trans Date” is Date/Time
Columns F = “FormType” is Text
Based on the following condition when a record is added to the “AccountActivity” table and the “Prefix” & the “RequisitionNbr” fields are NOT NULL, I want to create a code or a system that will search the other table “ReqLog” for the same “Prefix” & “RequisitionNbr” . If missing add that particular missing record from the “AccountActivity” table to the “ReqLog” table with Only the following fields.
Account
Prefix
RequisitionNbr
Trans Date
Example if the following recorded added to the “AccountActivity” table has an Account = 12345, BudgetCategory = 2, Voucher Nbr = D99001, REF Type = RS2, Encumbrance Nbr = 2, Prefix = BTT, RequisitionNbr = 135, Trans Date = 8/30/2004 and Obj Code Class = 205 , The code or system will check the “ReqLog” table for Prefix = BTT & RequisitionNbr = 135 and if missing, the record should be added to that table with only the Account field = 12345, Prefix Field = BTT, RequisitionNbr = 135 & Trans Date = 8/30/2004.
Can you please give me suggestions on how to accomplish this task or create a code. I am new to Databases and Codes. Thanks in advance.