• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • 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

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




0
 
peter57rCommented:
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 = ", "
Else
rs.MoveFirst
Do Until rs.EOF
GetAllCT = GetAllCT & rs!comparitortype & ", "
rs.MoveNext
Loop
End If
GetAllCT = Left(GetAllCT, Len(GetAllCT) - 2)

rs.Close
Set rs = Nothing

End Function





0
 
PeterBaileyUkAuthor Commented:
Expert advice as always thank you
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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