Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2016
    Posts
    1

    Unanswered: Merge Duplicate Record from 4 Tables

    i would like to seek the help of our more seasoned coder here is my scenario

    here is the code to query 4 tables (necessary to query all 4 tables)

    Code:
    SELECT * into tblDUPLICATE FROM ((CRIME LEFT JOIN SUSPECTS ON CRIME.CITE_NR = SUSPECTS.CITE_NR) LEFT JOIN OFFENSE ON CRIME.CITE_NR = OFFENSE.CITE_NR) LEFT JOIN VICTIMS ON CRIME.CITE_NR = VICTIMS.CITE_NR WHERE (Year([DATE_COMTD])= YEAR(DATE());
    heres my code to
    MERGE record with the same CITE_NR AND NOT same V_NO
    WILL NOT MERGE if same CITE_NR AND SAME V_NO

    Code:
    Do Until BasRecSet.EOF
        Do Until rsMerge.EOF
            SQLScript01 = "SELECT * FROM " & STR4Query & " WHERE CITE_NR like " & Trim(rsMerge!CITE_NR)
            Set strBravo = CurrentDb.OpenRecordset(SQLScript01)
            With strBravo
    			If BasRecSet.Updatable Then
    				If rsMerge!V_NO <> BasRecSet!V_NO Then
    					BasRecSet.Edit
    					BasRecSet!V_NAME = BasRecSet!V_NAME & ", " & BasRecSet!V_NAME
    					BasRecSet!V_SEX = BasRecSet!V_SEX & ", " & BasRecSet!V_SEX
    					BasRecSet!V_AGE = BasRecSet!V_AGE & ", " & BasRecSet!V_AGE
    					BasRecSet.Update
    				ElseIf rsMerge!S_NO  <> BasRecSet!S_NO  Then
    					BasRecSet.Edit
    					BasRecSet!S_NAME = BasRecSet!S_NAME & ", " & BasRecSet!S_NAME 
    					BasRecSet!S_SEX = BasRecSet!S_SEX & ", " & BasRecSet!S_SEX
    					BasRecSet!S_AGE = BasRecSet!S_AGE & ", " & BasRecSet!S_AGE
    					BasRecSet.Update						
    				End If
    			Else
    				MsgBox "Table Cannot Use for Updating!"
    			End If          
            End With
        rsMerge.MoveNext
        Loop
    BasRecSet.MoveNext
    Loop
    but not performing the way i needed the code to

    as shown from the screenshots...

    request.bmp

    i really need the advice of our more well versed and professional approach.....

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,095
    Provided Answers: 17
    You really need to take some steps backwards and redesign some tables - you ought not to have more than one data point in a cell. Doing so violates first normal form, and sets you up to fail further down the line.

    The query statement that you have posted will create a table called tblDuplicate - once you have run it, further attempts will fail (unless you delete it beforehand). I don't think that such SQL statements are valid as a source for recordsets.

    Without knowing the structures of the source tables or the structure of the required output, it's almost impossible to suggest a way forward.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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