Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004

    Unanswered: Adding a new record by comparing values in two tables

    Help!!! Code needed to add a new record to a table based on the following scenario

    I have two tables, one that has the Customer bio details and the other has the Customer Payment info. Both tables are linked via the CustomerID.

    I would like to read the entire Customer table and for each customer read check the Payment Info table to see if the CustomerID exists. If it does, check another field in the table to see if it matches the financial period you are interested in. If it does not, locate the next CustomerID and check the period as well. If the record with the CustomerID and Period you are interested in is not found, add a new record with the CustomerID and the financial period.


    Read Customer table. Select the first record CustomerID
    Read Customer Payment Info table. After finding the first occurrence of the matching CustomerID check the period field in the Payment Info table. The first record for the matching CustomerID has period = 2005/2006, the second record has period = 2006/2007. The period interested in is 2007/2008 for that particular customer. If that CustomerID and period does not exist, create a new record in the Customer Payment Info table with the CustomerID and Period = 2007/2008

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    Here's some code for ADO. Others might give you an example in DAO.

    What this does is this....
    It loops through the Customer table
    For each CustomerID in the Customer table, it searches the Payment table for that CustomerID AND FinancialPeriod (I combined this verses opening the Payment table and looping through each Payment record. It basically does the same thing but avoids having to loop through each record in the Payment table.)
    If not found, it adds a new record in the Payment table
    If found, it goes to the next CustomerID in the Customer table

    Dim rs As ADODB.Recordset 'Customer table recordset
    Dim ry As ADODB.Recordset 'Payment table recordset
    Set rs = New ADODB.Recordset
    Dim FinancialPeriodInterestedIn As String 'Assuming this is a string field otherwise use Date if a date field
    FinancialPeriodInterestedIn = Forms!MyFormName!MyFinancialPeriodField 'Field from a form
    Dim strSQL As String

    strSQL = "Select * from CustomerTable"
    strSQL = "Select CustomerID from CustomerTable"

    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    Do While Not rs.EOF
    Set ry = New ADODB.Recordset
    'For strSQL string below...
    'Use '" & FinancialPeriodInterestedIn & "'" for string matches
    'Use #" & FinancialPeriodInterestedIn & "#" for date matches
    strSQL = "Select * from PaymentTable where CustomerID = " & rs!CustomerID & " and FinancialPeriodField = '" & FinancialPeriodInterestedIn & "'"
    ry.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    If ry.BOF And ry.EOF Then
    MsgBox "No matching record in Payment table for Financial Period...Adding new Payment record."
    ry!CustomerID = rs!CustomerID
    ry!FinancialPeriodField = FinancialPeriodInterestedIn
    ry!OtherFinancialField = "Whatever"
    ry!OtherFinancialField = "Whatever"
    Set ry = Nothing
    MsgBox "Record exists in Payment table."
    Set ry = Nothing
    End If
    Set rs = Nothing
    msgbox "Search done."
    Last edited by pkstormy; 06-11-07 at 20:54.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts