asked on

Combine Unique Text Records from a Single Row into One Record Access 2007

I've got a query in Access 2007 that pulls a list of Projects that in some cases have multiple Bill Methods. I want to be able to combine the text of these Records into one record.

I've attached a Screenshot of the Access Data that I have.

I pasted the Data in Excel and manually combined the records to show what I'm looking for.

As you can see Project Numbers 383053 and 411062 have 2 different Bill Methods and I want to create a Function in access that combines them into one record much like the "Concatenate" Function in Excel.

HainKurt

I suggest you create a differnt table, say PaymentMethod (ProjectNumber, Billing_Method) and cleanup this table by removing the billing_method and duplicate data and insert those records into new table...

HainKurt

create a function like this

and call it in your query like this

select distinct project_number, companycode, getBillingMethods(project_number) BillingMethods
from myTable

Public Function getBillingMethods(project_number As Integer)
    Dim result As String
    Dim strSQL As String
    strSQL = "select * from myTable where project_number=" & project_number
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    While Not rs.EOF
      result = IIf(result = "", rs("Billing_Method"), result & ", " & rs("name"))
      rs.MoveNext
    Wend
    
    getBillingMethods = result
    Set rs = Nothing
End Function

Open in new window

SOLUTION

thomas-sherrouse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.