Types of JOIN

[Rodolfo Fontes]

Hi group,

I have a 2 queries, one for all products sold, and another to all products
bought. Also I have a table with some data for the products.
My question is:
How can I list all results from the two queries, since some products appear
on the the first, and not on the second.
My intetion is to make a sum and list the total avaible (bought-sold).

Can i make a querie using these 2 queries and the table to list all products
of both?

Thanks,
Rodolfo Fontes

 

[John Vinson]

Hi group,

I have a 2 queries, one for all products sold, and another to all products
bought. Also I have a table with some data for the products.
My question is:
How can I list all results from the two queries, since some products appear
on the the first, and not on the second.
My intetion is to make a sum and list the total avaible (bought-sold).

Can i make a querie using these 2 queries and the table to list all products
of both?

Thanks,
Rodolfo Fontes

I would suggest using a UNION query. You can't build it in the query
grid, you need to go to the SQL window to do it; but if you're summing
by product, it's probably the simplest approach. Assuming that you
have a query ProductsBought and another query ProductsSold with fields
ProductID and Quantity, where Quantity is a positive number, try:

SELECT ProductsBought.ProductID, ProductsBought.Quantity
FROM ProductsBought
UNION ALL
SELECT ProductsSold.ProductID, - ProductsSold.Quantity
FROM ProductsSold;

Save this query as uniBoughtAndSold, and then base a totals query on
it, grouping by ProductID and Summing quantity.

John W. Vinson[MVP]

 

[Rodolfo Fontes]

Thanks John!

That's exactly what i was looking for.

Do you, or anyone recommend a book for SQL?

Thanks,
Rodolfo Fontes