Rows into Columns

[chrisdavis]

I have a simple table with data like this:

Field1 Field2
00001 WC
00001 AT
00001 BC



What I need is to group by Field 1 and have WC, AT, BC on the same line as grouped Field1. I would prefer it be in the same column if possible but broken out would be cool too.

I have read some posts and crosstab won't work for me; there are 2,000 distinct values and it's too long.

[chrisdavis]

Oh, I would need code examples of VBA because I'm not VBA proficient. I have looked at other forums on Google and couldn't find what I was looking for.

[TheShabz]

I'm not quite sure what you want your output to look like. Can you provide an example?

[boblarson]

See if you can make out how to use this:
http://allenbrowne.com/func-concat.html

All you should need to do is to paste the provided function into a STANDARD module (not a form, report, or class module - making sure the module name isn't the same as the function or any other one). Then you just use it in a query and pass the appropriate fields to it. The page goes into how to use it but feel free to post back if you run into problems.

[chrisdavis]

Hi Shabz. Like this:

Field1 Field2
00001 WC, AB, ET

How many there are of Field2 with the same grouping as Field1

[chrisdavis]

Bob thanks for responding. I am looking through it now. I will prob. have questions though??

[chrisdavis]

Hey Bob, I'm kind of getting it after reading through it.
The query runs with this:
Expr1: concatrelated("Field12","Sheet1","","","-")

but it is duplicating the values many times in the Expr1 field. I may be doing something wrong...

[boblarson]

See if this works instead:

Expr1: concatrelated("Field12","Sheet1", , ,"-")

When you have no parameter information to pass to an optional parameter you do not include anything between the comma for it.

[chrisdavis]

I don't think it's looking through the recordset. How does the function know where to "stop" and "start"??

[chrisdavis]

Bob, I tried that and got a syntax error:

basically in the query, I have my one table "Sheet1" (just imported from Excel)

I have "totals" on
I want to "group by" Field1 (that's a policy number for example)
I then need (no matter how many there are), the values in Field12 to show up in the column separated by commas or dashes or slashes (whatever here).

So:
Field1 Field 12
00001 WC
00001 AT
00001 BR
00001 CO
00002 WC
00002 CO

To show up like this:
Column1 Column2
00001 WC, AT, BR, CO
00002 WC, CO

And so on...

[boblarson]

I don't think it's looking through the recordset. How does the function know where to "stop" and "start"??

Sorry, I had to test it because I hadn't used it in a while. So, here's my example one which worked. I have an Orders Table which I want to get all orders concatenated for each employee (in my Employees table).

So, I used this:
MyOrders: ConcatRelated("OrderID","Orders","[Orders].[EmployeeiD]=" & [EmployeeID],"","-")

But also have to have the [EmployeeID] field in my query so it can list each employee. And I had to use the Grouping for it to work. So here's my example SQL:

SELECT Orders.EmployeeID, ConcatRelated("OrderID","Orders","[Orders].[EmployeeiD]=" & [EmployeeID],"","-") AS MyOrders
FROM Orders
GROUP BY Orders.EmployeeID, ConcatRelated("OrderID","Orders","[Orders].[EmployeeiD]=" & [EmployeeID],"","-");

[chrisdavis]

Hey Bob. Sweet and thanks for the example...

I don't have a separate table, just values within the table: So I did this:

Expr1: ConcatRelated("Field12","Sheet1","[sheet1].[ID]=" & [sheet1].[ID],"","-")

It's still not getting the desired result:

SELECT Sheet1.Field1, ConcatRelated("Field12","Sheet1","[sheet1].[ID]=" & [sheet1].[ID],"","-") AS Expr1
FROM Sheet1
GROUP BY Sheet1.Field1, ConcatRelated("Field12","Sheet1","[sheet1].[ID]=" & [sheet1].[ID],"","-");

What do you think? I have edited this and attached the .ZIP file of my 2010 database...it's Query6.

I would love to know what I'm doing wrong!!

[boblarson]

The change is:


SELECT Sheet1.Field1, ConcatRelated("Field12","Sheet1","[Field1]=" & Chr(34) & [Field1] & Chr(34),"[Field1]","-") AS Expr1
FROM Sheet1
GROUP BY Sheet1.Field1, ConcatRelated("Field12","Sheet1","[Field1]=" & Chr(34) & [Field1] & Chr(34),"[Field1]","-");



And beware - this will take a little bit for it to run. It took 55 seconds for me.

[fcp]

Try union query and base a another query on the union result. for example if you have
Field 1 Field2 Field 3
1 A B
1 C D

It will become
Field 1 Fields
1 A
1 B
1 C
1 D

[boblarson]

Try union query and base a another query on the union result. for example if you have
Field 1 Field2 Field 3
1 A B
1 C D

It will become
Field 1 Fields
1 A
1 B
1 C
1 D

The only problem, fcp, is you didn't read the question correctly. The data currently is

Field1   Value
  1         A
  1         B
  1         C
  2         A
  3         B
  3         D
 
 
and the output the OP wanted was
 
Field1   Values
   1       A-B-C
   2       A
   3       B-D

And the function I gave accomplished that.