Access: Merging similar records

Question

I have a table with multiple entries for unique UnitNo and Dates (sometimes different notes as it is been filled by different investigators). I want to merge the notes column and Investigator to a single notes and investigation column:

UnitNo    Date           Notes                 Investigator
----------------------------------------------------------------------------
232       13/08/15       No major issues       Andy Barney
232       13/08/15       Door Defect           Andy Barney
781       21/08/15       No defect found       John Adams
781       21/08/15       Door Defect           Andy Barney
xxx       xxxxxxxx       xxxxxxxxxxx           xxxxxxxxxx

I want to basically get a single record for the records with similar UnitNo and Date and then combine the other two fields in such a way that if it is similar it doesn't duplicate:

UnitNo    Date           Notes                 Investigator
----------------------------------------------------------------------------
232       13/08/15       No major issues       Andy Barney
                         Door Defect

781       21/08/15       No defect found       John Adams
                         Door Defect           Andy Barney

xxx       xxxxxxxx       xxxxxxxxxxx           xxxxxxxxxx

I have checked different places for the answer and I found this code Allen Browne and Here. However, I receive an error when I use ConcatRelated(xxx) saying that the sub.[xxx] is not defined.

EDIT

Here is my Code for the second part:

Public Function MergeEquinoxImport()

Dim SQL1 As String
Dim qdfNew1 As QueryDef
Dim db As Database

Set db = CurrentDb

SQL1 = "SELECT Sub.[UnitNo],Sub.[Date], ConcatRelated([Notes], [TblPractise], " & _
    "[Date]='" & Sub.[Date] & "' And [UnitNo]='" & Sub.[UnitNo] & "') " & _
    "FROM (SELECT q.[UnitNo],q.[Date], FROM TblPractise " & _
    "AS q Group BY q.[UnitNo],q.[Date],) AS Sub Order " & _
    "BY Sub.[UnitNo],Sub.[Date];"

If acbDoesObjExist("Query1", acQuery) Then
    DoCmd.DeleteObject acQuery, "Query1"
End If

With db
    Set qdfNew1 = .CreateQueryDef("Query1", SQL1)
End With

End Function

@HansUp This is a very similar problem as the one that you answered in the link. Can you help me with this? I basically get an error when I want to use ConcatRelated(xxx) saying that Sub.[xxx], the value Sub is not defined. — Kamran, Aug 26 '15 at 08:41

score 2 · Accepted Answer · edited May 23 '17 at 12:14

2

You need function similar to group_concat in mysql.

In access we dont have function like group_concat :(

Bt, we can achieve it by following code:

  Select T.ColumnA
      , GetList("Select ColumnB From Table1 As T1 Where T1.ColumnA = " & [T].[ColumnA],"",", ") AS ColumnBItems
    From Table1 AS T
    Group By T.ColumnA;

Kindly refer below SO question for the same.It contains complete details. Microsoft Access condense multiple lines in a table

edited May 23 '17 at 12:14

Community

1
1

answered Aug 26 '15 at 09:12

seahawk

1,872
12
18

Thanks for your comment. I have a question regarding your answer: "Select T.UnitNo, T.Date, GetList(Select Notes From TblPractise As T1 Where T1.UnitNo= " & T.[UnitNo] ","","") AS ColumnBItems From TblPractise AS T Group By T.UnitNo, T.Date;" But this gives me an error saying that T in the bracket is not defined (variable not defined). – Kamran Aug 26 '15 at 09:23
@Kamran : I forgot to add link for details. I have updated it. Please refer. – seahawk Aug 26 '15 at 09:47
This seems to be the answer to my question. However, some of my fields are in the format of Memo and it gives me error of "Invalid Memo, OLE, or Hyperlink object in subquery 'Notes'" Notes is the one that I want to put together – Kamran Aug 26 '15 at 10:07

score 0 · Answer 2 · answered Aug 26 '15 at 08:57

0

This should be done in a front end application and not using sql. If you want to do it, the only way I can think of is to give serial number an reset it for each id and for each column you need to check if the sno=1 then display the value otherwise display empty

answered Aug 26 '15 at 08:57

Madhivanan

13,470
1
24
29

Thanks for your comment. I believe it can be possible. At the moment the issue that I have is just that this code takes 'All' the values in my Note fields and display all of the notes available for a single column. However, if I sort this out so it filter the whole table each time running for the unique UnitNo and save it, it will be fixed. My problem is that when I define the Sub as my datasource, I can refer to it when I use the Recordset. – Kamran Aug 26 '15 at 09:05

Access: Merging similar records

EDIT

2 Answers2