Difference query

[Kundan]

In the attached DB I have 2 queries Qry_NIssuedSum & Qry_NRecvdSum. I want the difference of these two which I tried in Qry_NDiff. But only 2 records become available. The difference of record 7 doesn't come. Please guide me.

[June7]

Change the JOIN types.

SELECT Qry_NIssuedSum.SumOfAmount AS I, Qry_NRecvdSum.SumOfAmount AS R, Tbl_NTransac.ID, [R]-[I] AS D
FROM (Qry_NIssuedSum RIGHT JOIN Tbl_NTransac ON Qry_NIssuedSum.ID = Tbl_NTransac.ID) LEFT JOIN Qry_NRecvdSum ON Tbl_NTransac.ID = Qry_NRecvdSum.ID;

[isladogs]

You are using INNER joins so only records common to both queries are shown.
Also, I suggest you use the Nz function to convert null records to zero for clarity

Doing these gives all 5 records

SELECT Nz([Qry_NIssuedSum].[SumOfAmount],0) AS I, Nz([Qry_NRecvdSum].[SumOfAmount],0) AS R, Tbl_NTransac.ID, [R]-[I] AS D
FROM (Qry_NIssuedSum RIGHT JOIN Tbl_NTransac ON Qry_NIssuedSum.ID = Tbl_NTransac.ID) LEFT JOIN Qry_NRecvdSum ON Tbl_NTransac.ID = Qry_NRecvdSum.ID;

However ID 5 & 6 have both items as null so you may wish to exclude those leaving ID 3,4,7

SELECT Nz([Qry_NIssuedSum].[SumOfAmount],0) AS I, Nz([Qry_NRecvdSum].[SumOfAmount],0) AS R, Tbl_NTransac.ID, [R]-[I] AS D 
FROM (Qry_NIssuedSum RIGHT JOIN Tbl_NTransac ON Qry_NIssuedSum.ID = Tbl_NTransac.ID) LEFT JOIN Qry_NRecvdSum ON Tbl_NTransac.ID = Qry_NRecvdSum.ID
WHERE (((Nz([Qry_NIssuedSum].[SumOfAmount],0))<>0)) OR (((Nz([Qry_NRecvdSum].[SumOfAmount],0))<>0));

If you're not bothered about having null values shown as blank then use

SELECT Qry_NIssuedSum.SumOfAmount AS I, Qry_NRecvdSum.SumOfAmount AS R, Tbl_NTransac.ID, [R]-[I] AS D
FROM (Qry_NIssuedSum RIGHT JOIN Tbl_NTransac ON Qry_NIssuedSum.ID = Tbl_NTransac.ID) LEFT JOIN Qry_NRecvdSum ON Tbl_NTransac.ID = Qry_NRecvdSum.ID
WHERE (((Qry_NIssuedSum.SumOfAmount) Is Not Null)) OR (((Qry_NRecvdSum.SumOfAmount) Is Not Null));

However fixing this doesn't affect the fact that your table structure is wrong
Both tables tbl_NIssued & tbl_NReceived should be merged with + or - values for Amount
Similarly you should not be saving the calculated differences in tbl_NTransac.
These should just be calculated using a query as & when needed

[Kundan]

You are using INNER joins so only records common to both queries are shown.
Also, I suggest you use the Nz function to convert null records to zero for clarity

Doing these gives all 5 records

SELECT Nz([Qry_NIssuedSum].[SumOfAmount],0) AS I, Nz([Qry_NRecvdSum].[SumOfAmount],0) AS R, Tbl_NTransac.ID, [R]-[I] AS D
FROM (Qry_NIssuedSum RIGHT JOIN Tbl_NTransac ON Qry_NIssuedSum.ID = Tbl_NTransac.ID) LEFT JOIN Qry_NRecvdSum ON Tbl_NTransac.ID = Qry_NRecvdSum.ID;

However ID 5 & 6 have both items as null so you may wish to exclude those leaving ID 3,4,7

SELECT Nz([Qry_NIssuedSum].[SumOfAmount],0) AS I, Nz([Qry_NRecvdSum].[SumOfAmount],0) AS R, Tbl_NTransac.ID, [R]-[I] AS D 
FROM (Qry_NIssuedSum RIGHT JOIN Tbl_NTransac ON Qry_NIssuedSum.ID = Tbl_NTransac.ID) LEFT JOIN Qry_NRecvdSum ON Tbl_NTransac.ID = Qry_NRecvdSum.ID
WHERE (((Nz([Qry_NIssuedSum].[SumOfAmount],0))<>0)) OR (((Nz([Qry_NRecvdSum].[SumOfAmount],0))<>0));

If you're not bothered about having null values shown as blank then use

SELECT Qry_NIssuedSum.SumOfAmount AS I, Qry_NRecvdSum.SumOfAmount AS R, Tbl_NTransac.ID, [R]-[I] AS D
FROM (Qry_NIssuedSum RIGHT JOIN Tbl_NTransac ON Qry_NIssuedSum.ID = Tbl_NTransac.ID) LEFT JOIN Qry_NRecvdSum ON Tbl_NTransac.ID = Qry_NRecvdSum.ID
WHERE (((Qry_NIssuedSum.SumOfAmount) Is Not Null)) OR (((Qry_NRecvdSum.SumOfAmount) Is Not Null));

However fixing this doesn't affect the fact that your table structure is wrong
Both tables tbl_NIssued & tbl_NReceived should be merged with + or - values for Amount
Similarly you should not be saving the calculated differences in tbl_NTransac.
These should just be calculated using a query as & when needed

Thanks for your advice on table structure. I am working on it.