Inner join sum from two tables

[steve87bg]

Heres the code I am working

SELECT nz(sum(table1.PDV1),0)- nz(sum(table2.PDV2),0) AS Sum
FROM table1 inner join table2 ON table1.id1=table2.id1
WHERE table1.date>= #1/1/2013# and table1.date<=#30/4/2013#

I cant get code to work. I dont know where i am wrong :banghead:
i keep getting 0 as a result

 

[plog]

First, Sum is a reserved word in access so naming a field it is a bad idea. Second, I would swap the nesting of your NZ and Sum functions to this:

SELECT Sum(NZ(table1.PDV1,0)- Sum(Nz(table2.PDV2,0) AS SumDifference

 

[steve87bg]

Thanks for replay Plog but that didnt help. Now i get null value

 

[steve87bg]

My table2 was empty so i didnt get any value. But now when i add values to my table2 i only get result for table2 but not for table1

 

[plog]

Can you post some sample data from your tables, the data you are getting from your query based on that sample data and the data you expect from your query based on that sample data? Use this format

table1
PDV1, id1
700, 19
338, 23

Do that for every table/query.