Outer Join Composite Key

[Lady_Jane]

Hi everyone,
I have two queries each returning the exact same fields but representing subsets of my original dataset.
The queries are qry_MinMax_Ref and qry_MinMax_Test and they have the following fields:
Region
Method


category
MinStressorValue
MaxStressorValue

All I am trying to do is take the two final columns from qry_MinMax_Ref and append them onto qry_MinMax_Test so that my final table will have:
Region
Method
Category (renamed Variable)
MinValueTest
MaxValueTest
MinValueRef
MaxValueRef

Combination of Region, Method, and Category is essentially my composite key on both tables.
All unique combinations of Region, Method, and Category in qry_MinMax_Ref are present in qry_MinMax_Test, hence the left join below.

The following query returns the correct number of records, but the appended columns are completely empty (see attached image).

SELECT qry_MinMax_Test.Region, qry_MinMax_Test.Method, qry_MinMax_Test.category AS Variable, qry_MinMax_Test.MinStressorValue AS MinValueTest, qry_MinMax_Test.MaxStressorValue AS MaxValueTest, qry_MinMax_Ref.MinStressorValue AS MinValueRef, qry_MinMax_Ref.MaxStressorValue AS MaxValueRef
FROM qry_MinMax_Test LEFT JOIN qry_MinMax_Ref ON qry_MinMax_Test.Method = qry_MinMax_Ref.Method AND qry_MinMax_Test.category = qry_MinMax_Ref.category AND qry_MinMax_Test.Region = qry_MinMax_Ref.Region
ORDER BY qry_MinMax_Test.Region, qry_MinMax_Test.Method, qry_MinMax_Test.category;

Thank-you!

[rpeare]

Are both queries using the same table? if so where's the primary key, that would make things super simple you'd just need to connect both queries via the primary key and then mess with the LEFT JOIN, RIGHT JOIN properties to get what you want.

[Lady_Jane]

The queries both use the same 2 queries.
I'm not sure how I would go about following your suggestion...
Here is what the two queries look like:

qry_MinMax_Ref

SELECT qry_MethodsUnion.Region, qry_MethodsUnion.Method, qry_UnionStressorData.category, Min(qry_UnionStressorData.RawD) AS MinStressorValue, Max(qry_UnionStressorData.RawD) AS MaxStressorValue
FROM qry_MethodsUnion INNER JOIN qry_UnionStressorData ON qry_MethodsUnion.UID=qry_UnionStressorData.UID
WHERE IIf(qry_MethodsUnion.Method Like "FP*",qry_MethodsUnion.Result=1,IIf(qry_MethodsUnion.Method Like "PCA*",qry_MethodsUnion.Result=1,IIf(qry_MethodsUnion.Method Like "B-*",qry_MethodsUnion.Result=1,IIf(qry_MethodsUnion.Method Like "P-*",qry_MethodsUnion.Result<=0.25))))
GROUP BY qry_MethodsUnion.Method, qry_UnionStressorData.Category, qry_MethodsUnion.Region
ORDER BY qry_MethodsUnion.Region, qry_MethodsUnion.Method, qry_UnionStressorData.category;

qry_MinMax_Test

SELECT qry_MethodsUnion.Region, qry_MethodsUnion.Method, qry_UnionStressorData.category, Min(qry_UnionStressorData.RawD) AS MinStressorValue, Max(qry_UnionStressorData.RawD) AS MaxStressorValue
FROM qry_MethodsUnion INNER JOIN qry_UnionStressorData ON qry_MethodsUnion.UID=qry_UnionStressorData.UID
WHERE IIF(qry_MethodsUnion.Method Like "FP*",qry_MethodsUnion.Result=0,IIF(qry_MethodsUnion.Method Like "PCA*",qry_MethodsUnion.Result=0,IIF(qry_MethodsUnion.Method Like "B-*",qry_MethodsUnion.Result >1, IIF(qry_MethodsUnion.Method Like "P-*",qry_MethodsUnion.Result>0.25))))
GROUP BY qry_MethodsUnion.Method, qry_UnionStressorData.Category, qry_MethodsUnion.Region
ORDER BY qry_MethodsUnion.Region, qry_MethodsUnion.Method, qry_UnionStressorData.category;

Thanks for your help,
I could have sworn I had this working before and now I come back to it, make a small change and it fails again

[boblarson]

What you would do is to create a query with JUST the Region, Method, and Category fields and then you would use the three queries with the two you posted joined to the one I suggested with an OUTER JOIN. So joined on Region, Method, and Category from my query with the arrow pointing to the other table. You would join the two queries to my query SEPARATELY - do NOT join your two queries together.

That will ensure that you have values for any possible combination where there isn't a value.

Otherwise, if you know there will be a matching value in both of your queries then you can just join the three fields Region, Method, and Category.

[Lady_Jane]

Thanks for the reply, Bob, but I'm not sure I understand exactly what you are trying to say.
Here is what I have attempted to do based on your suggestion:

What you would do is to create a query with JUST the Region, Method, and Category fields

I created qry_RMC:

SELECT qry_MethodsUnion.Region, qry_MethodsUnion.Method, qry_UnionStressorData.Category
FROM qry_MethodsUnion INNER JOIN qry_UnionStressorData ON qry_MethodsUnion.UID = qry_UnionStressorData.UID
ORDER BY qry_MethodsUnion.Region, qry_MethodsUnion.Method, qry_UnionStressorData.Category;

use the three queries with the two you posted joined to the one I suggested with an OUTER JOIN. So joined on Region, Method, and Category from my query with the arrow pointing to the other table.

And here I attempt to join both of my previously created queries (MinMax Test and Ref) to qry_RMC:
Unsurprisingly, this one won't run... "Join expression not supported"

SELECT qry_RMC.Region, qry_RMC.Method, qry_RMC.Category, qry_MinMax_Test.MinStressorValue AS MinTest, qry_MinMax_Test.MaxStressorValue AS MaxTest, qry_MinMax_Ref.MinStressorValue AS MinRef, qry_MinMax_Ref.MaxStressorValue AS MaxRef
FROM (qry_MinMax_Test LEFT JOIN qry_RMC ON (qry_MinMax_Test.Region = qry_RMC.Region AND qry_MinMax_Test.Method = qry_RMC.Method AND qry_MinMax_Test.Category = qry_RMC.Category)) RIGHT JOIN qry_MinMax_Ref ON (qry_RMC.Region = qry_MinMax_Ref.Region AND qry_RMC.Method = qry_MinMax_Ref.Method AND qry_RMC.Category = qry_MinMax_Ref.Category)
ORDER BY qry_RMC.Region, qry_RMC.Method, qry_RMC.Category;

I'm really quite lost...
I don't understand why the one I posted originally does not work. If I base the join on only two of the three fields (any two) then it runs.

[rpeare]

Is your data coming from an outside source and you're just a clearinghouse for information or is this data created by your database?

by looking at it you should have no problem connecting the data on the three fields (region, method, category). You say you get results if you link only 2 of the fields but when you link the third it gives you no results in the two added columns, have you tried linking every combination of 2 fields (region & method, region & category, method & category) and seeing if you get results for all of them?

Another thing to try is to give each query an alias for the linked fields for instance MMR_Region (for min_max_ref region) and using the TRIM function to make sure you're not getting any extra (invisible) characters stuck in your query

so in your Min_Max_Ref query you'd have a field

MMR_Region: trim(fieldname)

Then trying the query again.

[Lady_Jane]

Thank-you rpeare!

I changed "qry_MethodsUnion.Region" to "TRIM(qry_MethodsUnion.Region) AS Region" in qry_MinMax_Ref and it works!