Sum 2 quantity in 2 tables

[roystonteo via AccessMonster.com]

Dear All,

Currently i have these:
Table A: Part No, Open Quantity
Table B: Part No, In Quantity
Table C: Part No, Out Quantity

I need to have 2 queries:
1st) how do i sum Open Quantity and In Quantity?
2nd) how do i get the balance quantity? which is sum open quantity + IN
quantity minus OUT quantity

Please advise me.

Thank you

 

[pietlinden]

Dear All,

Currently i have these:
Table A: Part No, Open Quantity
Table B: Part No, In Quantity
Table C: Part No, Out Quantity

I need to have 2 queries:
1st) how do i sum Open Quantity and In Quantity?
2nd) how do i get the balance quantity? which is sum open quantity + IN
quantity minus OUT quantity

Please advise me.

Thank you

union the two tables in a query then do a sum on your union query.

 

[pietlinden]

Dear All,

Currently i have these:
Table A: Part No, Open Quantity
Table B: Part No, In Quantity
Table C: Part No, Out Quantity

I need to have 2 queries:
1st) how do i sum Open Quantity and In Quantity?
2nd) how do i get the balance quantity? which is sum open quantity + IN
quantity minus OUT quantity

Please advise me.

I would actually merge all the tables and include a field to hold the
type of each record {Open, In, Out}. Then you can use indexes and all
that. Will work MUCH faster than union queries. And your queries
become trivial. Create a totals query to sum IN, OUT, Open and then
use another query to do the simple math.that adds the totals.

 

[roystonteo via AccessMonster.com]

Hi Piet,

But if Table A and Table B has the same part number and quantity, the union
will not total this 2 up.
If Table A + B, the query is UNION SELECT quantity *1 or Select quantity. Am
i right?
For subtraction, the union select quantity *-1

Table A: Part No, Open Quantity
Table B: Part No, In Quantity

Dear All,

[quoted text clipped - 9 lines]

Please advise me.

I would actually merge all the tables and include a field to hold the
type of each record {Open, In, Out}. Then you can use indexes and all
that. Will work MUCH faster than union queries. And your queries
become trivial. Create a totals query to sum IN, OUT, Open and then
use another query to do the simple math.that adds the totals.