Query from two tables

[Guest]

I have a table (TABLE1) with a ProductNumber field as primary key,
ProductName and Quantity fields.
I have a second table (TABLE2) also with the same fields but the number of
records is different.

TABLE1 lists the entire product numbers known but quantities may be zero (no
stock) and TABLE2 lists only the existences (available stock). The quantities
are (hopefully) equal but sometimes they differ.

I would like to create a query, and a report, that would allow me to
immediately compare the quantities from the two tables, e.g. a report in a
spreadsheet appearance with ProductNumber, ProductName, TABLE1_Qty,
TABLE2_Qty and DIFFERENCE (subtraction between quantities).

Could anyone help me on how to achieve this? Thx in advance.

 

[Carl Rapson]

I have a table (TABLE1) with a ProductNumber field as primary key,
ProductName and Quantity fields.
I have a second table (TABLE2) also with the same fields but the number of
records is different.

TABLE1 lists the entire product numbers known but quantities may be zero
(no
stock) and TABLE2 lists only the existences (available stock). The
quantities
are (hopefully) equal but sometimes they differ.

I would like to create a query, and a report, that would allow me to
immediately compare the quantities from the two tables, e.g. a report in a
spreadsheet appearance with ProductNumber, ProductName, TABLE1_Qty,
TABLE2_Qty and DIFFERENCE (subtraction between quantities).

Could anyone help me on how to achieve this? Thx in advance.

How about:

SELECT TABLE1.ProductNumber, Table1.ProductName,
Table1.Quantity AS TABLE1_Qty,
Nz(TABLE2.Qty,0) AS TABLE2_Qty,
(Table1.Quantity - Nz(TABLE2.Qty,0)) AS DIFFERENCE
FROM TABLE1 LEFT JOIN TABLE2 ON TABLE1.ProductNumber=TABLE2.ProductNumber;

This assumes that every product always has an entry in Table1, but it
doesn't have to have an entry in Table2.

Carl Rapson

 

[Guest]

That was great Carl!

I believe it did the trick! You just saved me a lot of work from now on.
Thx a lot m8