Re: Cross-tab with multiple values
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Mon, 30 Jan 2006 15:59:17 -0500
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
"Chris A via AccessMonster.com" <u2552@uwe> wrote in message
news:5b24ab2044d4a@xxxxxx
> 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.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200601/1
.
- References:
- Cross-tab with multiple values
- From: Chris A via AccessMonster.com
- Cross-tab with multiple values
- Prev by Date: Re: Problem trying to do an update query in VBA
- Next by Date: RE: query criteria entered via a form
- Previous by thread: Re: Cross-tab with multiple values
- Next by thread: RE: query criteria entered via a form
- Index(es):
Relevant Pages
|
|
