Solved

Flatten and Concatenate data

Posted on 2004-04-11
11
1,132 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
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)

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

790 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