merge duplicates

Posted on 2011-05-07
Last Modified: 2013-11-27
I have a table that is appended to after some tests, what I need now is to merge those rows into a new table.

I have attached a spredsheet showing output from a query.

The key is [mvriscode] and the spreadsheet shows whats required in terms of a new row.

I need to do this in vba in access

docmd.mysql xxxxxxxx'

SELECT [Find duplicates for TblAllDataDifferences].[MVRIS CODE Field], TblAllDataDifferences.Introdiff, TblAllDataDifferences.MarqueDiff, TblAllDataDifferences.ModelRangeDiff, TblAllDataDifferences.RangeSeriesDiff, TblAllDataDifferences.CCDiff, TblAllDataDifferences.NomCCDiff, TblAllDataDifferences.DoorsDiff, TblAllDataDifferences.BodyTypeDiff, TblAllDataDifferences.TransmissionDiff, TblAllDataDifferences.FuelDiff, TblAllDataDifferences.AspirationDiff, TblAllDataDifferences.DriveTypeDiff, TblAllDataDifferences.DrivingAxleDiff, TblAllDataDifferences.ForwardGearsDiff, TblAllDataDifferences.SeatsDiff, TblAllDataDifferences.EngineModelDiff, TblAllDataDifferences.NoCylDiff, TblAllDataDifferences.ValvespercylDiff, TblAllDataDifferences.valvegearDiff, TblAllDataDifferences.powerkwDiff, TblAllDataDifferences.calcbhpDiff, TblAllDataDifferences.gvwDiff, TblAllDataDifferences.cabDiff, TblAllDataDifferences.vanroofDiff, TblAllDataDifferences.wheelbasetypeDiff, TblAllDataDifferences.variantDiff, TblAllDataDifferences.AbiMatchedStr2, TblAllDataDifferences.AbiMatchedStr, TblAllDataDifferences.CapMatchedStr, TblAllDataDifferences.ContinentalMatchedStr, TblAllDataDifferences.GlassMatchedStr, TblAllDataDifferences.HalfordMatchedStr, TblAllDataDifferences.IDSMatchedStr, TblAllDataDifferences.MichelinMatchedStr, TblAllDataDifferences.TechDocMatchedStr, TblAllDataDifferences.VividMatchedStr, TblAllDataDifferences.SMMTComparitorDate, TblAllDataDifferences.ComparitorType, TblAllDataDifferences.VividMatchedStr2, TblAllDataDifferences.TechDocMatchedStr2, TblAllDataDifferences.MichelinMatchedStr2, TblAllDataDifferences.IDSMatchedStr2, TblAllDataDifferences.HalfordMatchedStr2, TblAllDataDifferences.GlassMatchedStr2, TblAllDataDifferences.ContinentalMatchedStr2, TblAllDataDifferences.CapMatchedStr2
FROM [Find duplicates for TblAllDataDifferences] LEFT JOIN TblAllDataDifferences ON [Find duplicates for TblAllDataDifferences].[MVRIS CODE Field] = TblAllDataDifferences.[MVRIS CODE];

Open in new window

Question by:PeterBaileyUk

    Author Comment

    this is driving me mad the ee system is defaulting to hardware in access with identical sub divisions.
    LVL 77

    Expert Comment

    Are you any any/all of these fields can have multiple values?

    Author Comment

    the row with [comparitortype] 1 is the over riding data in essence the merged bits are the field [comparitor type] which becomes a concatenation of the field rows. so in this ex 1, 4 but could be 1, 4, 7

    the comparitor type field number tells me which client so 1 is me and others clients

    LVL 77

    Accepted Solution

    If it's just that one field then you can use a Group By query to create the output, using a vba function to do the concatenation of the multiple ComparitorType values.

    The query has the form...

    Select [Mvris Code field], max(Introdiff) as mxIntroDiff, max(.......and so on for each field) ,
    max(SMMTComparitorDate) as mxSMMTComparitorDate, first(getAllCT([Mvris code field])) as fsSMMTComparitorType
    Group By [Mvris Code field]

    The function would be...

    Function GetAllCT(vMvris) As String
    dim tablename as string
    tablename = " yourtablenamehere"  '**************

    Dim rs As DAO.Recordset
    GetAllCT = ""
    Set rs = CurrentDb.OpenRecordset("Select comparitortype from " & tablename & " where [Mvris Code field] = '" & vMvris & "' order by comparitortype")
    If rs.RecordCount = 0 Then
    ' do nothing except allow for comma to be removed
    GetAllCT = ", "
    Do Until rs.EOF
    GetAllCT = GetAllCT & rs!comparitortype & ", "
    End If
    GetAllCT = Left(GetAllCT, Len(GetAllCT) - 2)

    Set rs = Nothing

    End Function


    Author Closing Comment

    Expert advice as always thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Suggested Solutions

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Tired of waiting for your show or movie to load?  Are buffering issues a constant problem with your internet connection?  Check this article out to see if these simple adjustments are the solution for you.
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now