Results 1 to 5 of 5

Thread: Crosstab (97)

  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab (97)

    Crosstab
    Is it not possible to formulate a cross tab which reports on details as opposed to using aggregate functions? I have a column headings by region and row headings by type, but would like the results to be details separated by commas, say something like

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab (97)

    Use the 'Expression' aggregate function...I'm not 100% that this would work, but I think it might...worth a try.

    Cheers

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab (97)

    Crosstabs are groupby queries by definition. If you have column headings by region and row headings by type, what does the item1, item2, etc. represent? And what do you mean by "details"? Are you referring to the Value?
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab (97)

    I want data returned with multiple values in a single column.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab (97)

    You can use a custom function to concatenate values. But be warned in advance that the resulting crosstab query is likely to be very slow.

    Put the following custom function in a standard module:

    Function Concatenate(aRSet As String, aField As String, aCondition As String) As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strRes As String
    ' Open recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SELECT DISTINCT [" & aField & "] FROM [" & aRSet & "] " & _
    "WHERE [" & aField & "] IS NOT NULL AND " & aCondition & ";")
    ' Loop through recordset
    While Not rst.EOF
    strRes = strRes & ", " & rst(aField)
    rst.MoveNext
    Wend
    ' Get rid of first ", "
    If strRes <> "" Then
    strRes = Mid$(strRes, 3)
    End If
    ' Return result and clean up
    Concatenate = strRes
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Function

    Next, in the design view of your crosstab query, create a calculated field (don't break it into two lines):

    <pre>ConcatVal: Concatenate("[Source]","[ValField]",</pre>

    <pre>"[ColField]='" & [ColField] & "' AND [RowField]='" & [RowField] & "'")</pre>

    Substitute your own items as follows:
    <UL><LI>Source: name of source recordset (table or query)
    <LI>ValField: name of field you want to concatenate in the form Item1, Item2, ...
    <LI>ColField: name of field acting as column header
    <LI>RowField: name of field acting as row header[/list]I have assumed that RowField and ColField are text fields, so the values are enclosed in single quotes. For numeric fields, omit the single quotes.

    Set the grouping (totals) option for this calculated field to Expression and set the crosstab option to Value.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •