Flatten and Concatenate data

Posted on 2004-04-11
Last Modified: 2008-03-17
My goal is to take data that looks like this:
ID  Value
1      A
1      B
2      A
3      D
4      A
4      Z

and make it look like this:

ID  Values
1   A, B
2   A
3   D
4   A, Z

In other words, I want to flatten and concatenate the data for reporting purposes using queries (not code). The first listing is the result of a query.  I can use this query as the recordsource for a crosstab query and get to an intermediate result that looks something like this:

ID  A  B   D   Z
1   A   B
2   A
3            D
4   A           Z

My problem is getting to the final form. If I could predetermine what the ultimate values will be (the values are actually strings, but I am using alphas here for ease of description) the answer would be simple. I could hard-code a calculated field like this:

=IIF(ISNULL(A),"",A) +", " + IIF(ISNULL(B),"",B) +", " + IIF(ISNULL(D),"",D) +", " + IIF(ISNULL(Z),"",Z)

However I don't know what the values will be beforehand, so I need a solution that will work on the fly in Access. Thoughts?

In SQL Server I can do this using code a SELECT statement like this:

SET @outRoleIDList = ''

SELECT @outRoleIDList = @outRoleIDList + ',' + cast(RoleID as varchar(10))
FROM LoginsRoles
WHERE LoginID = @inLoginID

SET @outRoleIDList = STUFF(@outRoleIDList, 1, 1, '')  --remove leading comma

Question by:rgrimm
  • 4
  • 2
  • 2
  • +2
LVL 54

Expert Comment

ID: 10801176
The final form is exactly the output you get from a crosstable query.

Just place the table in the graphical query editor and place the ID field and the Value field twice.
Change the query type into crosstable and make the ID the rowheader, the first Value field the Column header and the second the value.
Finally change the last Value from GroupBy into Value

Clear ?


Accepted Solution

whitbacon earned 250 total points
ID: 10802106
You might chek out his link.  it is a module which is code, but not code for each query!!!  it may help
LVL 54

Assisted Solution

nico5038 earned 250 total points
ID: 10802174
Hmm, will a report as the final form do too ?
When the max number of columns is known, you might use some code to fill a report from a crosstable query dynamically.

Making the columnheader and detaildata flexible is possible, but needs some VBA code in the OpenReport event.

To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but this could differ for you.

Make sure that the number of Columns is not bigger as the number placed. The programcode has no protection against that !

The OpenReport code:

Private Sub Report_Open(Cancel As Integer)
Dim intI As Integer
Dim intR As Integer

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset(Me.RecordSource)

'Place headers
For intI = 3 To rs.Fields.Count - 1
Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
Next intI

'Place correct controlsource
For intI = 3 To rs.Fields.Count - 1
Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
Next intI

'Place Total field
Me.ColTotal.ControlSource = "=SUM([" & rs.Fields(2).Name & "])"

End Sub

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but it could differ for you.


Expert Comment

ID: 10802480
FROM LoginsRoles
PIVOT RoleID In ("A","B","D","Z")

Author Comment

ID: 10847908
I didn't really get the answer I was hoping for, but it was a failure of Access (the Jet database engine, actually), not the experts!

I was looking for a 100% query-based solution, and it turns out that it can't be done that way. You must use code. The code isn't difficult (see the code link in the Accepted Answer), but I was hoping for a query-based solution so that the end user coud deal with it more easily in the future.

The point that makes a query-based solution not doable is that you cannot pre-determine what all the values that would make up the column headers in the crosstab will be. Each time this query is run it will have a different set of values and number of values that must then be concatenated together into a comma-delimited fashion.

What Access/Jet needs is what SQL Server already has: the ability to create a variable, and then use that variable in a SELECT and keep appending values to it during the looping that naturally occurs in the SELECT process, as the Side Note above shows. This would obviously work in an Access Project (adp file) which uses SQL Server as the backend, but isn't included functionality in the Jet database engine (mdb file).

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Expert Comment

ID: 10847937
I thought the SQL I posted returned exactly what you're looking for :(

To go from that format to the exact format you're looking for is simply this:

SELECT LoginID, LEFT(IIF(a,"A,","") + IIF(b,"B,","") + IIF(d,"D,","") + IIF(z,"Z,",""), Len(IIF(a,"A,","") + IIF(b,"B,","") + IIF(d,"D,","") + IIF(z,"Z,","")) - 1) FROM Query1

Author Comment

ID: 10852002
The last sentence of my original post stated, "However I don't know what the values will be beforehand, so I need a solution that will work on the fly in Access." I don't know what values I will get, nor the number of values. Therefore I couldn't do as you suggested because I have no idea what to hard code. It needs to be created on the fly, like the SQL Server aside shows.

But thanks for taking the time! I appreciate it your giving it a shot!


Expert Comment

ID: 11605901
The code that is in the Accepted answer link works only if the fields to be concatenated are integers. What should be changed to make it work for all data types?

Author Comment

ID: 11608887
I guess I don't see where the code is only for concatenating integers. It uses the variant datatype, which will handle just about anything. Regardless, if you are having problems try surrounding the the section in the loop that fetches the value from the recordset with a CVar function to convert it to a variant. It would look something like this:

With rs
        If .RecordCount <> 0 Then
            'start concatenating records
            Do While Not rs.EOF
                varConcat = varConcat & CVar(rs(strFldConcat)) & ";"
        End If
    End With

Hopefully that will work for you. You shouldn't have to do anything with the first part of the code example, since that is just dynamically building an SQL string. Any conversion problems you might experience would happen in the above section which is where the flattening takes place.

Expert Comment

ID: 11614176 this one done : had to include DAO objects, nothing wrong with the code at all)

one more question:

say my query gives an answer for the tables I've created like this :
id    children    dogs
1     a,b,c        x,y
2     d,e,f        x,xx
3     a,p,q       w,ww

the tables are tusers, tdogs, tchildren and the query is :SELECT fconcatchild("tdogs","id","dog","long",[id]) AS dogs, fconcatchild("tchildren","id","child","long",[id]) AS children
FROM tusers;

say I want all details of the id's who have a dog named x i.e i want the query result as:

id  children  dogs
1  a,b,c       x,y
2  d,e,f       x,xx

how should i modify the above query?

Author Comment

ID: 11619040
I see a few alternative solutions:

* Add a WHERE clause: WHERE dogs LIKE "*x*"
* Use the table resulting from your SELECT fconcatchild query as the foundation for a second query, and then filter the results using the WHERE clause in the first alternative.

The first option should work, as Access' JET database engine will construct a result set with all records, and then use the WHERE clause to filter out all the unwanted records.

The easiest way to construct the second option is to build the query in the Access Query Designer and get the results you want, switch to SQL view, and then copy/paste the SQL into your VB code. Or, if you are like me, just use the query and forget putting it in code.

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

914 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now