Combining 2 tables in a query

[gert birkner]

I have to combine the data of 2 tables to show them in a list field with 3
columns:
stock table:
CustomerNo.
StockQuantity
if the StockQuantity= 0 there is now row for this customer in this table

order table
CustomerNo
OrderQuantity


CustomerNo is in both tables the primarykey.
I want to show for each CustomerNo the SUM(StockQuantity) and
SUM(OrderQuantity).
The problem is, that neither of these tables contains all of the existing
CustomerNo.
So there are rows in "stock" which don´t exist in "order" and rows in
"order" which don't exist in "stock".
My resultset should look like: (3 columns: CustomerNo; StockQuantity;
OrderQuantity)

100 3 0
101 0 4
102 2 4
....

I can´t formulate a query which show a result like that

thank you in advance

gert birkner

 

[Joseph Meehan]

I have to combine the data of 2 tables to show them in a list field
with 3 columns:
stock table:
CustomerNo.
StockQuantity
if the StockQuantity= 0 there is now row for this customer in this
table
order table
CustomerNo
OrderQuantity


CustomerNo is in both tables the primarykey.
I want to show for each CustomerNo the SUM(StockQuantity) and
SUM(OrderQuantity).
The problem is, that neither of these tables contains all of the
existing CustomerNo.
So there are rows in "stock" which don´t exist in "order" and rows in
"order" which don't exist in "stock".
My resultset should look like: (3 columns: CustomerNo; StockQuantity;
OrderQuantity)

100 3 0
101 0 4
102 2 4
...

I can´t formulate a query which show a result like that

thank you in advance

gert birkner

Maybe this will help from the help file:

Create the two tables that will have a many-to-many relationship.

Create a third table, called a junction table, and add to the junction table
new fields with the same definitions as the primary key fields from each of
the other two tables. In the junction table, the primary key fields function
as foreign keys. You can add other fields to the junction table, just as you
can to any other table.

In the junction table, set the primary key to include the primary key fields
from the other two tables. For example, in an Order Details junction table,
the primary key would be made up of the OrderID and ProductID fields.

How?
Open a table in Design view.

Select the field or fields you want to define as the primary key.

To select one field, click the row selector for the desired field.

To select multiple fields, hold down the CTRL key and then click the row
selector for each field.
Click Primary Key on the toolbar.

Note If you want the order of the fields in a multiple-field primary key
to be different from the order of those fields in the table, click Indexes
on the toolbar to display the Indexes window, and then reorder the field
names for the index named PrimaryKey.

Define a one-to-many relationship between each of the two primary tables and
the junction table.

 

[tomandla]

Couldn't he just to a union query to get all of the Customer Nos and
link the two tables to the union query and get all of the information
he is looking for? It seems like that might be easier???

 

[gert birkner]

I tried every form (I was able to think of) to combine thiese 2 tables with
UNION, but I got always a produkt of both tables which made the SUM
senseless.

But I found a solution without a third table.

I wrote an userdefined function in SQL Server (sorry I didn´t say that
first)
.............
RETURN(
SELECT CustomerNo, SUM(StockQuantity), 0 AS OrderQuantity FROM Stock ...
UNION
SELECT CustomerNo, 0 AS StockQuantity, SUM(OrderQuantity) FROM Order ....
)

and used it in my SELECT like
SELECT CustomerNo, SUM(StockQuantity), SUM(OrderQuantity)
FROM dbo.mynewfunction(parameters)
GROUP BY ...
ORDER BY ...

That did it!

Thank you all for your suggestions


gert birkner