Solved

Access Querry--Combine Records

Posted on 2014-10-10
7
154 Views
Last Modified: 2014-10-12
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
0
Comment
Question by:mdstalla
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 40

Expert Comment

by:als315
ID: 40374513
You can use DConcat function from Patrick Mattews article Domain Aggregate for Concatenating Values by Group in Microsoft Access
0
 

Author Comment

by:mdstalla
ID: 40375322
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
 

Author Comment

by:mdstalla
ID: 40375324
Forget the last sentence--my database is too big to share.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 40

Expert Comment

by:als315
ID: 40375349
Add Group By all fields to your query
0
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 40375353
Look at sample
DBConcat.accdb
0
 

Author Closing Comment

by:mdstalla
ID: 40375729
Duh... I knew I was forgetting to do something really simple.

This worked perfectly.  Thanks for all of your help.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40375774
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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 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