Composite/combined query results

[Brian]

Table #1 has fields A, B, & C (among others). C is a quantity.

Table #2 also has fields A, B, & C (and others that do not exist in Table #1).

I want to sum C for each AB combination, regardles of whether it is in
Table1, Table2, or both. SELECT DISTINCT AB in Table1 or Table2 is easy using
a regular join, as is SELECT DISTINCT AB in Table2 that is not in Table1,
using an unequal join.

How, though, can I get SELECT DISTINCT AB from Table1 OR Table@? I am trying
to avoid using appends because of the pitfalls/complexities of using
temporary appends in this multiuser environment.

 

[Jeff Boyce]

Brian

A couple of points...

First, if you have duplicated data in Table1 and Table2 (Fields A, B, C,
from the sound of it), you have a potential issue with synchronization.
Which one is the correct one?

If you have multiple tables to handle something like (multiple years,
multiple offices, multiple ...), your data structure needs further
normalization if you want to take advantage of Access' functionality. You
don't want to embed "data" in table names...

Finally, if you create two queries (one for T1, one for T2) that return the
A, B, and C values, you can use the SQL statements from those two to create
a third query. Take a look at UNION queries in Access HELP for more
explanation.

 

[Brian]

Let me clarify my A's & B's. This is a specialized product shipment system,
and it was originally built to apply shipments of products to purchase & sale
contract balances. Now a customer wants to bring product into inventory via
the shipment, and then a way to adjust the quantity of that product at that
location.

So now there are two ways a Quantity of a Product can affect the balance of
the product at a location: being shipped in/out or by being adjusted in/out.
Here are the germane fields (among many, many more that relate to the
contract side of the program):

Shipments table: Location (optional), Product (required), Quantity (required)
Adjustments table: Location (required), Product (required), Quantity
(required)

Now I need to find all distinct Location/Product combinations, regardless of
whether it comes from Shipments or Adjustments (or exists in both) so that I
can provide a report grouped by Location and with a SumOfQuantity for each
Product at that location.

I think I just have tunnel vision here. I will take a deeper look at your
notes, but if this clarification was not too complicated, I would appreciate
any more details it brings to mind.

Thanks.

 

[Brian]

You can ignore my other post. I wrote it before I had time to explore your
suggestion on Union queries. That fit like a glove. Thanks for sharing your
expertise.