Dsum (1 Viewer)

[spikepl]

Your naming is, unfortunately, a mess. Sort that out first.

Here is a naming convention you can choose to use: http://www.access-programmers.co.uk/forums/showthread.php?t=225837

Here is a list of words you should not use to name objects in your db: http://allenbrowne.com/AppIssueBadWord.html

Finally, do not use the same word for naming a table and naming a field in the table - that is absolutely confusing, and it will confuse you too in 14 days when you will have to make changes but forgot what was what.

 

[haroon.mansha]

Sorry for that. I will correct it. But can you suggest me what I can do for the solution

 

[Galaxiom]

Forget the DSum. An aggregate query is far more efficient.

The Inner Join is why you are missing some products.

SELECT Product.Product, SUM(Nz([In].QuantityIn)) AS QIn, SUM(NZ([Out].QuantityOut)) AS QOut
FROM (Product 
LEFT JOIN [In] ON Product.auto = [In].Product
) 
LEFT JOIN Out ON Product.auto = Out.Product
GROUP BY Product.Product;

Assuming auto is the PK of the Product table and Product is the corresponding FK of the other tables. (You really do need to look at how you choose table and field names.)

 

[haroon.mansha]

Thanks Galaxiom, i have tried your sql, it worked, but the results were not correct, It is showing the out column correct, but do not showing in column not correct, as it is multiplying it.

 

[haroon.mansha]

Just for example the data as follows (output needed as below with your sql)

Product in out
APPLE 10 5
MANGO 10 0


if the table in as follows
Product In
Apple 5
Mango 5
Apple 5
Mango 5

out Table
Apple 5

But the result is showing as below
Apple 10 10
Mango 10 0

now you can see as there are 2 entries in table for Apple, so it sum 2 times

 

[haroon.mansha]

Any solution will be appreciated

 

[Galaxiom]

Something weird going on then. Can you post a database with the relevant tables to demonstrate the problem?

 

[haroon.mansha]

The file is attached in Post #11 test.accdb

 

[haroon.mansha]

I am again attaching the file.