Access Querry--Combine Records

I have an Access Query in which I would like to combine fields within the same data-record so that multiple rows are not produced.  This happens because there is one field that could have more than one input and thus creates a new row for each of those inputs (with mostly duplicate information).

I've attached a spreadsheet that should give you an idea of exactly what I'm talking about.  Note that Client 3 has multiple License Numbers and thus creates multiple Rows in the report.  Can I combine this so that only one row is produced per client (with commas separating each License Number)?

If anyone could walk me through the steps on how to do this, it would be most appreciated.
Example.xls
mdstallaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
als315Connect With a Mentor Commented:
Look at sample
DBConcat.accdb
0
 
als315Commented:
You can use DConcat function from Patrick Mattews article Domain Aggregate for Concatenating Values by Group in Microsoft Access
0
 
mdstallaAuthor Commented:
I used his codes-- and while it does merge License No.'s it does not delete duplicate Clients (see attached).

Do you happen to have a code that I can pair with this one that will automatically delete all duplicate records after the merge is complete?

I've also included the database—it's under Query:  Table1 Query

Thanks.
Example.xls
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
mdstallaAuthor Commented:
Forget the last sentence--my database is too big to share.
0
 
als315Commented:
Add Group By all fields to your query
0
 
mdstallaAuthor Commented:
Duh... I knew I was forgetting to do something really simple.

This worked perfectly.  Thanks for all of your help.
0
 
hnasrCommented:
Building on database uploaded by als315 (with thanks), as an example

Check Query2 as cross tab from Table1.
TRANSFORM Table1.[License No#]
SELECT Table1.Client, Table1.Name, Table1.DOB
FROM Table1
GROUP BY Table1.Client, Table1.Name, Table1.DOB
PIVOT Table1.[License No#];

Open in new window


Query3 produces the required output, using function newClient(Client As Variant) in Module1.
SELECT Query2.Client, newClient([Client]) AS licenses, Query2.Name, Query2.DOB
FROM Query2;

Open in new window

Function newClient(client As Variant) As String
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Query2 WHERE Client ='" & client & "'")
    Dim i As Integer
    Dim s As String
    s = ""
    For i = 3 To rs.Fields.Count - 1
        If Not IsNull(rs.Fields(i)) Then
            If s = "" Then
                s = rs.Fields(i)
            Else
                s = s & "," & rs.Fields(i)
            End If 's not empty
        End If ' field not null
    Next
    newClient = s
End Function

Open in new window

DBConcat-2.accdb
0
All Courses

From novice to tech pro — start learning today.