Cross-tab with multiple values

  • Thread starter Thread starter Chris A via AccessMonster.com
  • Start date Start date
C

Chris A via AccessMonster.com

Hi-

I am looking to create a matrix - cross-tab style query or view (can be a
report). I am not really looking for summarized fields though. I want the
actual data or datas in a cell to appear mabye concatenated in one cell/field
in the cross-tab report so my table looks like so.

Market Type Promo
A B A1
A B A2
A B B3
B A B1
B A B2

Market is my row heading Type is my column heading. Now I would like to
return like this...

B A
A A1 A2 B3
B B1 B2

Here is my query I know it is wrong it has the idea but I think I need to
have these in an array or recordset and that may work I am not sure. Any help
would be appreciated.
 
Hi,


Here an answer by Steve Dassin (1998)

=================
Hello Stephen,
A relatively easy way to concatenate fields within a group can be
accomplished using a crosstab query and a user-defined function.


For your second example, you can use the following crosstab query:


TRANSFORM Max(myfield2) AS [Value]
SELECT myfield1,Count([Value]) AS cnt,
Max(String1([Value],cnt)) AS concat
FROM mytable
GROUP BY myfield1
PIVOT myfield2 IN (Null);


And user-defined function:


Public Function string1(ID As String, Count As Integer) As String
Static j As Integer
Static y As String
j = j + 1
y = Switch(y = "", ID, True, y & "," & ID)
If j = Count Then
string1 = y
j = 0
y = ""
End If
End Function


With result :


myfield1 concat
Friends Bert,Ernie
Tools Chisel,Hammer


The query can easily be modified to handle duplicate rows.
If you would like further examples/explanations of how the crosstab query
can
be used to answer questions like this E-mail me. You can also request the
documents
"Heavy Duty Crosstabs with Access 97" and "Crosstab Query Examples" which
contain examples of this technique.


HTH
Steve Dassin


========================





Hoping it may help,

Vanderghast, Access MVP
 
Both work well I used the first example as it was more applicable to my
situation but great work. Thanks so much! By the way great way to make a
matrix style report.

Michel said:
Hi,

Here an answer by Steve Dassin (1998)

=================
Hello Stephen,
A relatively easy way to concatenate fields within a group can be
accomplished using a crosstab query and a user-defined function.

For your second example, you can use the following crosstab query:

TRANSFORM Max(myfield2) AS [Value]
SELECT myfield1,Count([Value]) AS cnt,
Max(String1([Value],cnt)) AS concat
FROM mytable
GROUP BY myfield1
PIVOT myfield2 IN (Null);

And user-defined function:

Public Function string1(ID As String, Count As Integer) As String
Static j As Integer
Static y As String
j = j + 1
y = Switch(y = "", ID, True, y & "," & ID)
If j = Count Then
string1 = y
j = 0
y = ""
End If
End Function

With result :

myfield1 concat
Friends Bert,Ernie
Tools Chisel,Hammer

The query can easily be modified to handle duplicate rows.
If you would like further examples/explanations of how the crosstab query
can
be used to answer questions like this E-mail me. You can also request the
documents
"Heavy Duty Crosstabs with Access 97" and "Crosstab Query Examples" which
contain examples of this technique.

HTH
Steve Dassin

========================

Hoping it may help,

Vanderghast, Access MVP
Hi-
Click to expand...
[quoted text clipped - 22 lines]
help
would be appreciated.
Click to expand...
Click to expand...
 
Back
Top