• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1138
  • Last Modified:

Flatten and Concatenate data

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

  • 4
  • 2
  • 2
  • +2
2 Solutions
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 ?

You might chek out his link.  it is a module which is code, but not code for each query!!!  it may help

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.

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

FROM LoginsRoles
PIVOT RoleID In ("A","B","D","Z")
rgrimmAuthor Commented:
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).

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
rgrimmAuthor Commented:
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!

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?
rgrimmAuthor Commented:
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.
okie...got 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?
rgrimmAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now