Solved Subtract two table (1 Viewer)

amir0914

Registered User.
Local time
Today, 04:12
Joined
May 21, 2018
Messages
151
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:12
Joined
May 7, 2009
Messages
19,169
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.
Local time
Today, 04:12
Joined
May 21, 2018
Messages
151
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
Local time
Today, 07:12
Joined
Feb 19, 2002
Messages
42,970
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
Local time
Today, 04:12
Joined
Oct 29, 2018
Messages
21,357
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.
Local time
Today, 04:12
Joined
May 21, 2018
Messages
151
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
Local time
Today, 11:12
Joined
Feb 19, 2013
Messages
16,553
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:12
Joined
May 7, 2009
Messages
19,169
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
Local time
Today, 04:12
Joined
Oct 29, 2018
Messages
21,357
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.
Local time
Today, 04:12
Joined
May 21, 2018
Messages
151
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.
Local time
Today, 04:12
Joined
May 21, 2018
Messages
151
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.
 

amir0914

Registered User.
Local time
Today, 04:12
Joined
May 21, 2018
Messages
151
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
Local time
Today, 11:12
Joined
Feb 19, 2013
Messages
16,553
I create a textbox on the subform and whose name is "Cnt"
it is the textbox's controlsource that should be populated with 'cnt'
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:12
Joined
Feb 19, 2002
Messages
42,970
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
Local time
Today, 04:12
Joined
Oct 29, 2018
Messages
21,357
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.
 

Users who are viewing this thread

Top Bottom