Combining fields from multiple rows into one row & field

[bassmantweed]

Hello All,

I am trying to figure out the best was to combine fields from multiple rows into one row & field.

Example: I have a table that contains footnotes and products. With a simple query I would get the following 3 rows:

Product..................................................Footnote
V.I. Capital Appreciation Fund.................3
V.I. Capital Appreciation Fund.................5
V.I. Capital Appreciation Fund.................1

What I want is one row and the 3 footnotes combines into one field:

Product..................................................Footnote
V.I. Capital Appreciation Fund.................3, 5 ,1

Any help would be greatly appreciated.

 

[pbaldy]

One way in a report:

http://support.microsoft.com/kb/210163/en-us

To do in a query, you'll need a VBA function that accepts the Product value as a parameter and returns the concatenated list of Footnotes.

 

[bassmantweed]

Thanks, I thought about the report option but I need this in a query so I can use the data for something else.

 

[boblarson]

Thanks, I thought about the report option but I need this in a query so I can use the data for something else.

May we ask for what?

 

[bassmantweed]

I need the data to create a property file for a web application. The final output will be to a .csv and the footnotes will be wrapped in a <Sup> 1,3,5, </Sup> for the web page to list the product then then footnotes.

 

[pbaldy]

Well, like I said, you'll need a function. Use a recordset loop of the Footnotes for the given Product and build a string to pass back to the query. Let us know if you run into trouble.

 

[boblarson]

This is just a suggestion for a way to use the report and still do what you need. If you export the report to CSV and then use VBA to open the text file and add the additional HTML that you need.

That's just a suggestion.

 

[bassmantweed]

My VBA skills are somewhat limited is there a place where I could learn how to do this or "borrow" some code?

 

[pbaldy]

Something like this in a standard module:

Public Function Concat(Product As String) As String
  Dim rs As DAO.Recordset
  Dim strSQL As String
  Concat = ""
  strSQL = "SELECT Footnote FROM TableName WHERE Product = '" & Product & "';"

  Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
  Do While Not rs.EOF
    Concat = Concat & rs("Footnote") & ";"
    rs.MoveNext
  Loop
  Concat = Left(Concat, Len(Concat) - 1)
  Set rs = Nothing
End Function

Called from the query in a new field:

Concat(Product)

 

[bassmantweed]

Paul, Thanks so much...... i think I am almost there:

When i rn the query it says : "Undefined Function 'ConCat' in expression?

 

[boblarson]

Did you make sure to put the function in a STANDARD module and NOT a form module?

 

[bassmantweed]

From the main screen I selected Modules & clicked new, then proceeded to enter the code above and changed it to my exact field & table names: (I said product & Footnote in my original question as I thougt that was easier)

Table = Footnotes
Field1 = Fund
Field2 = Number

Public Function Concat(Product As String) As String
Dim rs As DAO.Recordset
Dim strSQL As String
Concat = ""
strSQL = "SELECT Number FROM footnotes WHERE Fund = '" & Fund & "';"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Do While Not rs.EOF
Concat = Concat & rs("Number") & ";"
rs.MoveNext
Loop
Concat = Left(Concat, Len(Concat) - 1)
Set rs = Nothing
End Function

I saved the module as Concat

I then built a query with the footnotes table and selected two output fields:

Fund & Footnote_Nos: Concat([Number])

 

[pbaldy]

You don't want the module having the same name as the function.

 

[bassmantweed]

Ok. I renamed the module to something unique and that seemed to get me a little further along...... Now the query runs but I get a different error: "Runtime Error '5': Invalid proceedure call or argument."

These lines are highlited in yellow when I choose debug:
Concat = Left(Concat, Len(Concat) - 1)
Set rs = Nothing

I owe you a couple of virtual pints for all of your help.

 

[bassmantweed]

I forgot to mention that when the query runs I have to hit end once for every row and when the query is complete all the rows have the fund name and #Error in the number field.

 

[pbaldy]

I would expect this:

Fund & Footnote_Nos: Concat([Number])

to be

Fund & Footnote_Nos: Concat([Fund])

You want to pass the fund, and the function should find the numbers associated with that fund.

 

[bassmantweed]

Same Error :-(

 

[pbaldy]

Can you post the db?

 

[bassmantweed]

Hey PBALDY.....

Unfortunately I can not post the whole database but I have extracted the one table and taken out All of the records except 11 for demo's sake:

You can see it is listed a s
Fund-------------------------------------Number
Capital American Fund------------------5
Capital American Fund------------------3
Capital American Fund------------------1
Capital American Fund II---------------7
Capital American Fund II---------------1
Core Equity Fund------------------------3
Core Equity Fund------------------------1
Mid Cap Core Equity Fund---------------7
Mid Cap Core Equity Fund--------------1
Small Cap Equity Fund------------------6
Small Cap Equity Fund------------------1


What I need is
Fund-------------------------------------Number
Capital American Fund------------------1,3,5
Capital American Fund II---------------1,7
Core Equity Fund------------------------1,3
Mid Cap Core Equity Fund---------------1,7
Small Cap Equity Fund------------------1,6


I have also removed the module as when i run it on my machine it creates havoc. i have pasted it below:

Public Function Concat(Product As String) As String
Dim rs As DAO.Recordset
Dim strSQL As String
Concat = ""
strSQL = "SELECT Number FROM footnotes WHERE Fund = '" & Fund & "';"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Do While Not rs.EOF
Concat = Concat & rs("Number") & ";"
rs.MoveNext
Loop
Concat = Left(Concat, Len(Concat) - 1)
Set rs = Nothing
End Function


Thanks again for all of your help.

 

[pbaldy]

Sorry, didn't get a notification that you had replied. This what you're after? Several little typos, but one thing I hadn't mentioned was that you needed a reference to DAO set in VBA.