Join Quandry 1

[LHWC]

I need to match hardware to windows. There are two suitable hardware types for each window, and many sizes of each. I have a working query for each hardware type. They select the hardware size based on the weight and height of the window. The ranges are not the same between the two, so one window may only appear in the type A query, while another may only appear in the type B query. I would like to incorporate them into a single query that makes a choice between the two hardware types. Select type A for all windows that appear in both queries, and the appropriate type for windows that appear only in one query or the other.
A LEFT JOIN selects all A's and those that can use A or B. A RIGHT JOIN selects the converse.
How do I select all A's with no matching B's AND all B's with no matching A's, as well as those that have A's and B's?
Thanks in advance.

 

[hneal98]

You did not mention what the INNER JOIN selects. It selects only A in both queries or only B in both queries.

If your problem is more complex, you will need to show an example of the data and what you are trying to get.

 

[JonFer]

You want to do a full outer join which Access doesn't support directly but can do in a few steps. If you didn't already, search for "FULL OUTER" in this forum - the question has appeared before.

If you don't find enough help in your search, post sample data from QueryA and QueryB as hneal98 suggests.

 

[LHWC]

Thanks for the direction.
To answer your question, an INNER Join returns only those records that appear in both queries, not any that appear in only one.
I briefly looked at some other posts re: Full Outer. They point to a Union Join. I'll try some of those suggestions, and post back if I still have problems.
Thanks again!

 

[JonFer]

A union (but not UNION ALL) query will remove duplicates so it is a good choice to get all of the unique key fields from the different tables/queries. Then you use the Union as the "Left" table of a Left Join query to get a Full Outer join result.

 

[LHWC]

A left join, with a couple of IIF expressions, followed by a union all and a right join where query A.ID is null seems to have done the trick.
Thanks to both of you and Rudy (r937)for all the help.
Now all I have to do is combine the hardware for the upper and lower window sash.........

 

[LHWC]

Turns out that combining the upper and lower queries is more problematic than anticipated. I structured a third query similar to the other two; left join, union, right join. I get an error saying "System resource exceeded."
This is baffling because there is less data in the two FROM queries than the queries referenced by them.
Any ideas?
Again thanks for all assistance!

 

[lespaul]

again, why don't you provide some sample data from your tables and what your expected results are. It's a whole lot easier to help when we can "see" what you see.

Leslie

 

[LHWC]

Here's the SQL of the Top hardware query.

SELECT DISTINCT IIf([Top Balance.Balance #] Is Null,[Top Double Balance.Balance #],[Top Balance.Balance #]) AS [Top Balance #], IIf([Top Balance.NEW_TYPE] Is Null,[Top Double Balance.NEW_TYPE],[Top Balance.NEW_TYPE]) AS TYPE, IIf([Top Balance.SumOfFQTY] Is Null,2*[Top Double Balance.SumOfFQTY],2*[Top Balance.SumOfFQTY]) AS QTY
FROM [Top Double Balance] LEFT JOIN [Top Balance] ON [Top Double Balance].NEW_TYPE = [Top Balance].NEW_TYPE
UNION ALL SELECT DISTINCT IIf([Top Balance.Balance #] Is Null,[Top Double Balance.Balance #],[Top Balance.Balance #]) AS [Top Balance #], IIf([Top Balance.NEW_TYPE] Is Null,[Top Double Balance.NEW_TYPE],[Top Balance.NEW_TYPE]) AS TYPE, IIf([Top Balance.SumOfFQTY] Is Null,2*[Top Double Balance.SumOfFQTY],2*[Top Balance.SumOfFQTY]) AS QTY
FROM [Top Double Balance] RIGHT JOIN [Top Balance] ON [Top Double Balance].NEW_TYPE = [Top Balance].NEW_TYPE
WHERE [Top Double Balance].NEW_TYPE IS NULL
ORDER BY TYPE;

The Bottom hardware query is similar. Both return the desired results. The third query is to consolidate the Top and Bottom results. For example, Query 1 returns 2 A's, 4 B's, 6 C's and Query 2 returns 1 A, 6 B's,5 D's. Query 3 should return 3 A's, 10 B's, 6 C's, 5 D's. The SQL for Query 3 is:

SELECT DISTINCT [Bottom Balance Union].[Bottom Balance #], [Top Balance Union].[Top Balance #], [Bottom Balance Union].TYPE, [Top Balance Union].TYPE, [Bottom Balance Union].QTY, [Top Balance Union].QTY
FROM [Bottom Balance Union] LEFT JOIN [Top Balance Union] ON [Bottom Balance Union].TYPE = [Top Balance Union].TYPE
UNION ALL SELECT DISTINCT [Bottom Balance Union].[Bottom Balance #], [Top Balance Union].[Top Balance #], [Bottom Balance Union].TYPE, [Top Balance Union].TYPE, [Bottom Balance Union].QTY, [Top Balance Union].QTY
FROM [Bottom Balance Union] RIGHT JOIN [Top Balance Union] ON [Bottom Balance Union].TYPE = [Top Balance Union].TYPE
WHERE [Bottom Balance Union].TYPE IS NULL;

As I said this returns an error stating system resource exceeded.

Thanks to all!

 

[PHV]

Why not create a summing query of an union query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

Try these two queries instead of your single left/union/right query that combines top/bottom unions.

Create a query that gives you the unique types from the 2 unions. "Union" (as opposed to "Union All") automatically removes duplicates so you don't need to add "Distinct" here.

qryAllTypes:

Select Type From [Top Balance Union]
Union
Select Type From [Bottom Balance Union]

Final query:

Select t.[Balance#], b.[Balance#], ...
From ([Top Balance Union] as t Right Join qryAllTypes as a
On a.Type = t.Type) Left Join [Bottom Balance Union] as b
on a.Type = b.Type


This method can be used for your top/bottom unions as well and I believe will be more efficient at the cost of additional queries.

 

[eburg]

Thanks JonFer,
Your approach is much clearer, even if there are more queries.
I tried to run the qryAllTypes and I still get the system resource exceeded error. Any ideas as to what causes this or how to get around it?

 

[JonFer]

Here are a couple of things to try.

1. Instead of Union All with Distinct in your left/union/right top and bottom union queries do this:

Select ...
Union
Select ...

Union removes duplicates already so you don't need distinct.

2. qryAllTypes can also select from all 4 original source tables to get the unique Types. I'm pretty sure that will work. It will probably be more efficient, too, because it doesn't have to do all those IIFs to get the result.

3. Create a query like qryAllTypes for the top and bottom unions and then create new top and bottom queries like the final query above. This also gets rid of a lot of IIF processing.