Difference of 2 tables row sum

N

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
 
J

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
 
M

Marshall Barton

naveen said:
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.
 
N

naveen prasad

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

thankyou very much for your kind response.

regards
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top