# SolvedSubtract two table (1 Viewer)

#### amir0914

##### Registered User.
Hi all, I have two tables with similar fields, first table is stock of products and the second table is count of sold products, like this :

 Table 1​
 Field 1​ Field 2​
 A1​ 50​ A1​ 30​ A2​ 40​ A3​ 20​ A3​ 60​ A2​ 70​
 Table 2​
 Field 1​ Field 2​
 A1​ 5​ A2​ 4​ A2​ 2​ A1​ 8​ A3​ 7​

And now I want to create this query : (Subtract each sum product with together, for example to A : (50 +30)- (5+8) = 67)
 Query​
 Field 1​ Field 2​ A1​ 67​ A2​ 104​ A3​ 73​

Last edited:

#### arnelgp

Join the two table in query:
but first create a query that will bring all product (supposed) from Field 1 on both table:

qryAllProducts (query):

select distinct [Field 1] As Prod From table1
UNION
select distinct [Field 1] from table2

use qryAllProducts and table1 and table2, to create another query (FinalQuery)

SELECT qryAllProducts.Prod,
(select sum([field 2]) from table1 as a where a.[field 1]=[prod])-(select sum([field 2]) from table2 as a where a.[field 1]=[prod]) AS [Field 2]
FROM qryAllProducts;

#### amir0914

##### Registered User.
Thank you so much arnelgp , Can you write all the codes with SQL, without build any query ?

Like this :

Code:
``````dim pSQL as string

pSQL = "SELECT qryAllProducts.Prod,
(select sum([field 2]) from table1 as a where a.[field 1]=[prod])-(select sum([field 2]) from table2 as a where a.[field 1]=[prod]) AS [Field 2]
FROM (select distinct Field1 from Table1)"``````

#### Pat Hartman

##### Super Moderator
Staff member
You need three queries or you can use subqueries if you can work them out.

q1
Select fld1, Sum(fld2) as SumFld2
From tbl1
Group by fld1

q2
Select fld1, Sum(fld2) as SumFld2
From tbl2
Group by fld1

q3
Select q1.fld1, q1.SumFld2 - q2.SumFld2 as DifFld2
From q1 inner join q2 on q1.fld1 = q2.fld1

#### theDBguy

##### I’m here to help
Staff member
Hi. I wonder if this will work.
SQL:
``````SELECT Table1.Field1, Sum(Table1.Field2)-Sum(Table2.Field2) AS Diff
FROM Table1
INNER JOIN Table2
ON Table1.Field1=Table2.Field1
GROUP BY Table1.Field1``````

#### amir0914

##### Registered User.
Hi. I wonder if this will work.
SQL:
``````SELECT Table1.Field1, Sum(Table1.Field2)-Sum(Table2.Field2) AS Diff
FROM Table1
INNER JOIN Table2
ON Table1.Field1=Table2.Field1
GROUP BY Table1.Field1``````
Thanks theDBguy , it's working without any error but the result of subtract is not correct.

#### CJ_London

##### Super Moderator
Staff member
think union query is the way to go

SELECT field1, sum(field2)
FROM (SELECT Field1, field2 from table 1
UNION ALL SELECT Field1,-Field2 from table2) AS U
GROUP BY Field1

#### arnelgp

Code:
``````Dim pSQL As String
pSQL = "Select A.Prod, (Select Nz(Sum([Field 2]),0) " & _
"From Table1 As B Where B.[Field 1]=A.[Prod])-" & _
"(Select Nz(Sum([Field 2]),0) From Table2 As B Where B.[Field 1]=A.[Prod]) As [Field 2] " & _
"From (Select Distinct [Field 1] As Prod From Table1 " & _
"Union Select Distinct [Field 1] From Table2) As A;"``````

#### theDBguy

##### I’m here to help
Staff member
Thanks theDBguy , it's working without any error but the result of subtract is not correct.
Hi. Sorry to hear that. What does "not correct" mean? If the result is backwards, then try switching it around.

For example, switch A-B to B-A

#### amir0914

##### Registered User.
think union query is the way to go

SELECT field1, sum(field2)
FROM (SELECT Field1, field2 from table 1
UNION ALL SELECT Field1,-Field2 from table2) AS U
GROUP BY Field1
That was great, this work well in query, but why not work in vba?

Code:
``````pSQL = "SELECT field1, sum(field2) AS [Cnt] FROM (SELECT field1, field2 from table 1 UNION ALL SELECT field1,-field2 from table2) AS U GROUP BY field1"
me.Child01.Form.RecordSource = pSQL``````
Cnt is textbox on subform.

#### amir0914

##### Registered User.
Hi. Sorry to hear that. What does "not correct" mean? If the result is backwards, then try switching it around.

For example, switch A-B to B-A
No, it's not backwards, the calculated number is very different from the actual deducted number.

#### CJ_London

##### Super Moderator
Staff member
but why not work in vba?
think it probably does - but are your objects really called 'field1' and 'table 1'etc

#### amir0914

##### Registered User.
think it probably does - but are your objects really called 'field1' and 'table 1'etc
No, but I change the fields and tables name to the original names. the SQL code is running without any error, but I think the problem is this part :
sum(field2) AS [Cnt]
I create a textbox on the subform and whose name is "Cnt", but after run SQL code this field is empty.

#### CJ_London

##### Super Moderator
Staff member
I create a textbox on the subform and whose name is "Cnt"
it is the textbox's controlsource that should be populated with 'cnt'

#### amir0914

##### Registered User.
it is the textbox's controlsource that should be populated with 'cnt'
Wooooov,,, A big thanks.

#### Pat Hartman

##### Super Moderator
Staff member
Just FYI, the reason that the dbGuy's solution doesn't work is because tbl1 has many rows that match many rows in tbl2 so the join produces a Cartesian product. 5 rows in a times 4 rows in b grossly inflates the sums. You need to reduce each table to one row per id before you do the join which is why I used three queries.

A Union query will work if you multiply tbl2 entries by -1. That allows you to sum all the rows. But that still leaves you with two queries. The union and a query to sum the results of the union.

#### theDBguy

##### I’m here to help
Staff member
Just FYI, the reason that the dbGuy's solution doesn't work is because tbl1 has many rows that match many rows in tbl2 so the join produces a Cartesian product. 5 rows in a times 4 rows in b grossly inflates the sums. You need to reduce each table to one row per id before you do the join which is why I used three queries.

A Union query will work if you multiply tbl2 entries by -1. That allows you to sum all the rows. But that still leaves you with two queries. The union and a query to sum the results of the union.
Hi Pat. Thanks for the explanation.

#### Pat Hartman

##### Super Moderator
Staff member
You are quite welcome