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
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
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.
addendum - alternatively use the concatrelated function you can find here instead of left(well,1) http://allenbrowne.com/func-concat.html
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
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.
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.