• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

merge duplicates

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

  • 3
  • 2
1 Solution
PeterBaileyUkAuthor Commented:
this is driving me mad the ee system is defaulting to hardware in access with identical sub divisions.
Are you any any/all of these fields can have multiple values?
PeterBaileyUkAuthor Commented:
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

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

PeterBaileyUkAuthor Commented:
Expert advice as always thank you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now