Difference of 2 tables row sum

[naveen prasad]

Dear all,

kindly help me in this.

I have created 2 tables d1,d2

d1 fields are x1,x2;

d2 fields are y1,y2;

All fields are numeric values.

I want to subtract sum(y1) - sum(x1).

for example like below
x1 x2 y1 y1
d1- 20 30 d2- 100 20
10 20 10 10
10 10 10 15


I want to differentiate the sum of y1 -sum of x1

= 120-40 = 80

pls note. there is no primary key matching in both tables.


kindly help me in this case.
regards

 

[Jeff Boyce]

Take a look at Access HELP for "Totals" queries. You could create two
totals queries, one for each table, then a third query that takes the
difference between the amounts derived by the two totals queries.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Marshall Barton]

I have created 2 tables d1,d2

d1 fields are x1,x2;

d2 fields are y1,y2;

All fields are numeric values.

I want to subtract sum(y1) - sum(x1).

for example like below
x1 x2 y1 y1
d1- 20 30 d2- 100 20
10 20 10 10
10 10 10 15

Where are you trying to do this? More context and
background might lead to a more useful answer.

In a simple query, you could use:
SELECT Sum(x1) - DSum("y1", "d2") FROM d1

Note that if you change that from SQL view back to design
view, Access may mess with any WHERE clause.

 

[naveen prasad]

Dear Marshal,
Excellent reply I have executed your formula it worked 100% accurate.

thankyou very much for your kind response.

regards