Hello Everyone!
I am trying to find out the difference between two sum using the following query.
SELECT Query1.*, Temp.*
From
(
Query1,Temp inner join Dsum("Amount","Query1",”State =“txtState” AND Type = “txtType” AND Prod = 250 WHERE [Years] <=" & [Years]) -
Dsum("Amount","Query1","State =“txtState” AND Type = “txtType” AND Prod = 251 AND Prod = 252 AND PT = 29 WHERE [Years] <=" & [Years]) As Difference on Temp
) ;
The idea is, the button "validate" in ValidateFrm should take (from subform2) e,g
-->state i.e “MD” ,Type “PE”, Product "250" year "2003" should take its Amount "149" e.g as "Variable A"
-->state i.e “MD” ,Type “PE”, Product "251 & 252" year "2003" and take its Amount "34+98" e.g as "Variable B"
-->Lastly the difference must be calculated and shown in subform2 as "DIFF = A-B" in "Amount" column.
-Hence all product for all years should be calculated.
*The sample "QueryPic2" is just a reference mode; for the structure required , the values are not correct.
** Apart from query , any ideas about VBA code is appreciated.
*** See attached Access file for reference.
Please Help. Thank you.
I am trying to find out the difference between two sum using the following query.
SELECT Query1.*, Temp.*
From
(
Query1,Temp inner join Dsum("Amount","Query1",”State =“txtState” AND Type = “txtType” AND Prod = 250 WHERE [Years] <=" & [Years]) -
Dsum("Amount","Query1","State =“txtState” AND Type = “txtType” AND Prod = 251 AND Prod = 252 AND PT = 29 WHERE [Years] <=" & [Years]) As Difference on Temp
) ;
The idea is, the button "validate" in ValidateFrm should take (from subform2) e,g
-->state i.e “MD” ,Type “PE”, Product "250" year "2003" should take its Amount "149" e.g as "Variable A"
-->state i.e “MD” ,Type “PE”, Product "251 & 252" year "2003" and take its Amount "34+98" e.g as "Variable B"
-->Lastly the difference must be calculated and shown in subform2 as "DIFF = A-B" in "Amount" column.
-Hence all product for all years should be calculated.
*The sample "QueryPic2" is just a reference mode; for the structure required , the values are not correct.
** Apart from query , any ideas about VBA code is appreciated.
*** See attached Access file for reference.
Please Help. Thank you.
Attachments
Last edited: