Solved

Flatten and Concatenate data

Posted on 2004-04-11
11
1,129 Views
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?


SIDE NOTE:
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

0
Comment
Question by:rgrimm
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 54

Expert Comment

by:nico5038
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 ?

Nic;o)
0
 
LVL 3

Accepted Solution

by:
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

http://www.mvps.org/access/modules/mdl0004.htm
0
 
LVL 54

Assisted Solution

by:nico5038
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.

Nic;o)
0
 
LVL 7

Expert Comment

by:brgivens
ID: 10802480
TRANSFORM COUNT(*) > 0
SELECT LoginID
FROM LoginsRoles
GROUP BY LoginID
PIVOT RoleID In ("A","B","D","Z")
0
 

Author Comment

by:rgrimm
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).

0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:brgivens
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
0
 

Author Comment

by:rgrimm
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!

0
 

Expert Comment

by:eros_sunny
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?
0
 

Author Comment

by:rgrimm
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)) & ";"
                .MoveNext
            Loop
        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.
0
 

Expert Comment

by:eros_sunny
ID: 11614176
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?
0
 

Author Comment

by:rgrimm
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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

10 Experts available now in Live!

Get 1:1 Help Now