Dsum (1 Viewer)

[haroon.mansha]

I am trying to make a dsum for products coming in and coming out.pls find the example below

Products In
Apple 10
Mango 5

Products out
Apple 5

Now I want to show statement for sum as follows

Product. In. Out. Net in hand
Apple. 10. 5. 5
Mango. 5. 0. 5


But it is showing only

Product. In. Out. Net in hand
Apple. 10. 5. 5


Can you help me what I understand is dsum cannot find the out field for mango as there is no field found for mango
Any idea how to do it

Thanks in advance

 

[llkhoutx]

How are you doing this? In a bound or unbound form? In the query of a table? Your method is not obvious tome, but you're smarter than I am.

Since your question is in the Query section, I presume that you're trying to use a query.

I'd build a query, changing the "out" values to negative, then Union that query with the "in" values; then group and sum that query.

 

[haroon.mansha]

I am doing it in query. I am using dsum. I have testing one thing more.if I remove out table then it shows both apple and mango. As soon showed out table again it shows apple

 

[haroon.mansha]

Can you give idea in detail about union query for this matter. Infact in my example there is no value in whole out table for product mango. While in table have it thus it will show dsum as null and will not show it

 

[llkhoutx]

SELECT -[Apples] AS ApplesS, -[Mangos] AS MangosS FROM tbProductsOut
UNION
SELECT [Apples] AS ApplesS, [Mangos] as MangosS FROM tbProductsIn;

Note that resultant columns must have the same names. Group and sum the union query.

Good luck,

 

[haroon.mansha]

Thanks for helping. I think you did not understand my means. The only thing I want to show the null values as zero in query which is showing total sum for every product .I showed as a sample above that due to null it is omiting the product mango
I have tried your idea but how to link it with product

 

[llkhoutx]

A Null will not sum, use NZ([fieldName],0).

 

[haroon.mansha]

What I wanted I used in form and it works. But in query not working

 

[llkhoutx]

What I posted still applies, especially to a query. Try it.

 

[haroon.mansha]

Pls wait I upload my database hope you can guide me then

 

[haroon.mansha]

Pls find the attachment and check the query Net.

Why it is not showing Product Mango

 

[llkhoutx]

I don't open databases from people or sites I don't know; malware, hacks, etc.

 

[llkhoutx]

Post the SQL for your query.

 

[haroon.mansha]

Is there anyway I can assure you that file is safe? Anyway I am posting SQL view hope you can understand

 

[llkhoutx]

None, nada, nil, nyet!

 

[haroon.mansha]

SELECT Product.Product, Nz(DSum("quantityin","in","product=" & [auto]),0) AS Expr1, Nz(DSum("quantityout","out","product=" & [auto]),0) AS Expr2
FROM (Product INNER JOIN [In] ON Product.auto = [In].Product) INNER JOIN Out ON Product.auto = Out.Product;

 

[llkhoutx]

You need to NZ the table field, not the query result.

 

[haroon.mansha]

Still not working dear friend

 

[haroon.mansha]

Only the product which have entry in out table are showing else the product only in out or in table not showing

 

[haroon.mansha]

Brother all I can say I can take photos thus you can understand my point.. I have even tried with table field nz not working. Surely some mistake from my side