Min/Max of Transposed and Grouped Values

[Lady_Jane]

Hi everyone,


I could use some guidance on how to "transform" two tables.
My database contains data on stream sampling locations identified by UIDs. These locations either pass (1) or fail (0). There are different methods of determining whether a sampling location passes or fails.

Table one (tbl_StressorData) contains the raw data used to determine whether a UID passes or fails. It contains UID, region (one of two), and 8 stressor variables.

Table two (qry_Filter_AH) is actually a query. This query "analyses" the stressor data using 11 different methods (denoted by P10V4, P25V1, P25V2 etc...) and returns either a pass (1) or a fail (0) under each method for each sampling location. This table contains pass fail results only for one of the two regions (algonquin highlands [AH]).

What I would like to do is create a table that has as the first column all of the 11 methods. The subsequent columns would be Min(RLPC), Max(RLPC) etc... for all stressor variables up to Min(Stressor8), Max(Stressor8).
The complicating factor is that for each method I want only the min and max stressor variables of the "passing" sites.

So for method P10V4 I want Min(all stressors) and Max(all stressors) where qry_Filter_AH.P10V4 = 1.
For method P25V1 I want Min(all stressors) and Max(all stressors) where qry_Filter_AH.P25V1 = 1.

I hope this makes sense, any comments or guidance is appreciated. The attached screenshot shows the original table and query that I am working from.

[June7]

Getting multiple columns of data into a common column requires UNION query or VBA code writing to a table. No designer or wizard for UNION, must type into the SQL View window of query designer.

SELECT [Name], UID, "RLPC" As Category, RLPC As RawD FROM tbl_StressorData
UNION SELECT [Name], UID, "Stressor5", Stressor5 FROM tbl_StressorData
UNION SELECT [Name], UID, "Stressor6", Stressor6 FROM tbl_StressorData
UNION SELECT [Name], UID, "Stressor7", Stressor7 FROM tbl_StressorData
UNION SELECT [Name], UID, "Stressor8", Stressor8 FROM tbl_StressorData;

Join to the qry_Filter_AH on the UID field (actually, remove the filter and data from both areas will be available). Use this query as basis for grouping with aggregate calcs and filtering. Actually, remove the filter and data from both areas will be available.

[Lady_Jane]

Thanks for your help.
The union query works but I'm not sure how to use it. As my row headers in the final table I want to use all the different methods from qry_Filter_AH such as P10V4, P25V1, P25V2 etc...
Then have min/max, min/max etc... going across the top for each stressor variable.
How can I do this with the query that you posted?

Having a separate table for each region is no problem for me.
The attached screenshot shows the two filter queries along with the result of the union query that you posted.

Thanks.

[Lady_Jane]

I figured out the last part myself. I used the same format as the posted union query to convert qry_Filter_AH to a vertical table with columns for method, UID, and pass/fail. Then I created two crosstab queries from that one and the one posted above. One calculating the min and one calculating the max, followed by a select query to bring them together