Results 1 to 6 of 6
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    Concat field values in crosstab query?

    when I use cross tab query I would like to see the concatenated values in the Well field as A2, A3, A4 or A2-A4. Thank you in advance


    Well Well_sort F_Ct1 F_Ct2 F_Ct3
    A2-A4 1 28.21 27.73 28.02
    E2-E4 5 38.4 36.49 37.3
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    your table does not look right for the task required since x2 is always mapped to F-Ct1 so I suspect this is an over simplified example.. However assuming this is what you actually have, you want to group 'all the 'A's 'B's etc, in your crosstab query, you need to create a calculated column in your crosstab query being left(Well,1) and use that as your rowheading and remove Well from your query.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    addendum - alternatively use the concatrelated function you can find here instead of left(well,1) http://allenbrowne.com/func-concat.html

  4. #4
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Thank you. Concatrelated partially work. Because my well field is having all the values like E2, E3, E4, E5, E6, E7, A2, A3, A4, A5, A6, A7, B2, B3, B4, B5, B6, B7, F2, F3, F4, F5, F6, F7, C2, C3, C4, C5, C6, C7, D2, D3, D4, D5, D6, D7, A1, B1, A12, B12
    How can I limit them with in the query? As I want to group them by well_sort and not use any form filters mentioned in the AllenBrown's example

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,795
    Using a CROSSTAB:

    TRANSFORM Max(Wells.Cnum) AS MaxOfCnum
    SELECT Left([Well],1) AS WellGrp, Min(Mid([well],2)) AS WellMin, Max(Mid([well],2)) AS WellMax, Wells.well_sort
    FROM Wells
    GROUP BY Left([Well],1), Wells.well_sort
    PIVOT Wells.rank;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Wow!! That was amazing. It works perfectly without using concatRelated function.
    Thank you so much for taking time to read my question and solving it with simple solution.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 05-10-2014, 10:05 AM
  2. Replies: 7
    Last Post: 04-28-2014, 07:09 AM
  3. Last Name, First Name Concat Field
    By brianmcleer in forum Access
    Replies: 2
    Last Post: 06-05-2013, 12:18 PM
  4. Concat field names in Update SQL
    By Deutz in forum Queries
    Replies: 8
    Last Post: 09-21-2011, 05:43 PM
  5. How to sum values in Crosstab query?
    By Buakaw in forum Queries
    Replies: 3
    Last Post: 03-22-2011, 01:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums