Create a custom query from two other queries

[Bancell]

I have been fighting with tying these two tables together for a couple of days. I have two tables that contain part numbers. One table contains what the technicians current stock is set at.

Example Item # Min Max on hand qty
A12345 2 4 5

This table is being filtered from a form for date and tech name.

The second table contains a sum by Item # of the parts used by the tech.

Example Item # SumofQty
A123456 4



The challenge is that I need to be able to combine these two tables and have all item #'s show up so I can set criteria as:


Min Max OnHandQty SumOfQty
>0 or >0 or >0 or >0


Where I run into the problem is that both tables do not contain all the Item #'s of the other table and regardless of the type of join I select I am dropping Item #'s from one table or another. The only solution I came up with which Im not even sure is possible is to sum how tell the second table to insert a zero when it doesnt have a record to match table one.

Any suggestions?

 

[xenou]

untested, but basically you can create a virtual table unioning these two:

	select 
		[Item #] as ItemNum_, 
		Nz([Min],0) As Min_, 
		Nz([Max],0) as Max_, 
		Nz([on hand qty],0) as OnHandQty_, 
		0 as SumOfQty_
	from 
		Table1

	union all

	select 
		[Item #] as ItemNum, 
		0 As Min_, 
		0 as Max_, 
		0 as OnHandQty_, 
		SumOfQty as SumOfQty_

Conveniently, zero's won't change the final results so you can just sum up the quantities to build a "combined" dataset.

select 
	[A.ItemNum_] as ItemNum
	Sum(A.Min_) As MyMin,
	Sum(A.Max_) As MyMax,
	Sum(A.OnHandQty_) as MyOnHandQty,
	Sum(A.SumOfQty_) as MyTechCurrentQty
from
(
	select 
		[Item #] as ItemNum_, 
		Nz([Min],0) As Min_, 
		Nz([Max],0) as Max_, 
		Nz([on hand qty],0) as OnHandQty_, 
		0 as SumOfQty_
	from 
		Table1

	union all

	select 
		[Item #] as ItemNum, 
		0 As Min_, 
		0 as Max_, 
		0 as OnHandQty_, 
		SumOfQty as SumOfQty_
) A
group by A.ItemNum_

Access cannot handle nested queries with identical field names as the outer queries so I go to some extra lengths to avoid naming fields the same thing (although we could probably rename them back in the final result to their original names .... i.e., as long as each outer query has different names from its immediate subqueries they can kind of switch back and forth).