Solved Difference between two Dsum for Validation (1 Viewer)

RizS

New member
Local time
Today, 13:24
Joined
Apr 23, 2020
Messages
2
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.
 

Attachments

  • Database xxx.zip
    309.3 KB · Views: 118
Last edited:

Ranman256

Well-known member
Local time
Today, 06:24
Joined
Apr 9, 2015
Messages
4,337
you don't need to use Dsum in a query. Queries ARE the dsum.
make Q1 to do the sum,
Q2 to add other data linked to Q1.
 

RizS

New member
Local time
Today, 13:24
Joined
Apr 23, 2020
Messages
2
Thank you Ranman. I am trying using SUM, hope it works
 

Users who are viewing this thread

Top Bottom