Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Apr 2002
    Location
    Burlington, Washington, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cross tab value (2000)

    Is it possible to show "A, B, C " as values field in a crosstab query rather than a sum total of numbers?

    Larry

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

    Re: Cross tab value (2000)

    A crosstab query is a special form of a Totals query. The Value field must always use an aggregate function, such as Sum or Avg. Depending on the setup of your query, you might use the First or Last function to display individual values.

  3. #3
    Lounger
    Join Date
    Apr 2002
    Location
    Burlington, Washington, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross tab value (2000)

    Hans,

    Is there a way to concatenate values in a crosstab query to show values separated by commas i.e. (green, blue, red) as one field? I used the Last function to display individual values as you had mentioned.

    Thank you,
    Larry

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

    Re: Cross tab value (2000)

    Can you explain how and where you want to use this? I have trouble visualizing what you want to do.

  5. #5
    Lounger
    Join Date
    Apr 2002
    Location
    Burlington, Washington, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross tab value (2000)

    I need a quey that calculates the following information into a report (Company, Year, Product Size), where product size equals fluid onces.

    ___________2001______ 2002 _____ 2003 _____ 2004
    CompanyA__ 45, 16_____45, 24, 32__12________12, 48
    CompanyB___45, 32____ 12, 16_____16_________16, 32, 48


    Thank you,
    Larry

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

    Re: Cross tab value (2000)

    You need a concatenation function for this. You will find the code for such a function attached to <post#=301393>post 301393</post#>. Copy the code into a standard module.

    Open the crosstab query in design view. Change the value field to:

    Concat("TableName","ProductSize","Company=" & Chr(34) & [Company] & Chr(34) & " AND Year=" & Nz([Year],0))

    Replace TableName by the name of the source table for the query.
    I have assumed that Company is a text field and Year is a number field.
    Change the Total option for the Value field to Expression.

Posting Permissions

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