[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2011-05-12
7
Medium Priority
?
212 Views
Last Modified: 2012-05-11
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.

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

Excel Screenshot of Combined Data
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.
0
Comment
Question by:thomas-sherrouse
  • 4
  • 3
7 Comments
 
LVL 61

Expert Comment

by:HainKurt
ID: 35747133
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...

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35747266
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

0
 

Assisted Solution

by:thomas-sherrouse
thomas-sherrouse earned 0 total points
ID: 35747276
I figured out a way to do this in Excel that will work. I'm plaining on Linking the the Access query from Excel anyway.

So if you just use this Formula to Compare Project Numbers and see if they Equal and if so then pull the Bill Method. You can set up a Concatenate Formula to Compile the Text.

=IF(Table1[[#This Row],[PROJECT_NUMBER]]=OFFSET(Table1[[#This Row],[PROJECT_NUMBER]],-1,0),OFFSET(Table1[[#This Row],[BILLING_METHOD]],-1,0),"")
BillMethod.xlsx
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.

 
LVL 61

Accepted Solution

by:
HainKurt earned 1000 total points
ID: 35747278
a small fix
Public Function getBillingMethods(project_number As Integer)
    Dim result As String
    Dim strSQL As String
    strSQL = "select Billing_Method from myTable where project_number=" & project_number & " order by Billing_Method"
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    While Not rs.EOF
      result = IIf(result = "", rs("Billing_Method"), result & ", " & rs("Billing_Method"))
      rs.MoveNext
    Wend
    
    getBillingMethods = result
    Set rs = Nothing
End Function

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35747290
put above code in Access, press Alt+F11 and copy paste code into Modules (create a new module if you dont have one)
0
 

Author Comment

by:thomas-sherrouse
ID: 35747333
I want to accept HainKurt's Solution as well as mine for assisting me.
0
 

Author Closing Comment

by:thomas-sherrouse
ID: 35775466
I'll use both the Excel Version and Access in my Query.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

591 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