DSUM PROBLEM 1

[desikan]

I have a query where I would like to use DSUM to get the total qty of each item under various reasons shown in "reason" field.

Though the query gives a result, answers are absurd.

Following is the sql--

SELECT DISTINCTROW Table8.item, DSum("[qty]","table8","[reason] = 'loan in'&quot AS Expr1, Table8.part, DSum("[qty]","table8","[reason] = 'loan out'&quot AS Exp2, DSum("[qty]","table8","[reason] = 'direct'&quot AS Expr3
FROM Table8
GROUP BY Table8.item, Table8.part;

I have used Dsum in form/report so far but not in query.

Any help will be appreciated

 

[JonFer]

You are missing the criteria in the DSUM to only include the records that match the item and part being looked at.

However, you should use IIf statements instead. They are more efficient and simpler to use.

Sum(IIf([Reason]="loan in",[qty],0)) as Expr1
Sum(IIf([Reason]="loan out",[qty],0)) as Expr3

 

[desikan]

Hi, JonFer,
I tried your suggestion and it worked beautifully.Thanks a lot.

I have just a few questions,if you may spend few minutes for me I would be grateful.

The 1st part of your answer said about missing the criteria. I just want to know what exactly was my mistake?

Also can you please tell me little more about this IIf statement as to how it is better than DSUM?

Thanks a lot once again for sparing your time for me.

 

[desikan]

Hi, Jonfer
I think my reply got buried below other threads over the week end.

If you are reading this perhaps you would educate me little about IIf statement.

Thanks in advance for your help

 

[dhookom]

desidan,
You might need to add
DSum("[qty]","table8","[reason] = 'direct' AND Item=" & [Item] & " AND Part=" & [Part])
This expression may differ depending on your data types.

The IIf() statement provided by JonFer is much more efficient than using a DSum() or other doman aggregate function since it uses the report's recordset rather than creating new ones. The expression kinda explains itself if you just carefully read it and maybe check Help on the IIf() function.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[desikan]

Thanks Duane for your reply.

I think my idea is getting little more clear now. DSum can be better used in forms/reports for example where answer is required in one single field and of course in this case IIf() function did my job perfectly.

I will go through Help also for reading further about the IIf() function.

Thanks once again