I would appreciate help with a Dsum Problem please. I want to calculate a
Grand Total from a series of subtotals but don't seem to have understood the
Dsum Function properly.

I've used the following in a query

SELECT [RawMaterials].[Item],
[RawMaterials].[UnitCost],[RawMaterials].[AmountUsed],

[RawMaterials].[UnitCost]*[RawMaterials].[AmountUsed] AS TotalCost,

Dsum([TtalCost],"RawMaterials Query") AS GrandTotal

FROM RawMaterials;

and produce the following numbers in the GrandTotal Column which don't make
sense to me. Could anyone explain what's happening please and how to
produce a Grand Total?

Item UnitCost AmountUsed TotalCost Grand Total
A 0.1 6 0.6 3
B 0.2 5 1 5
C 0.15 10 1.5 7.5
D 0.5 1 0.5 2.5
E 0.7 2 1.4 7

Thankyou for your time

Stuart

Hi:
Please tell me what you want to see.
I don't believe that you can get what I think you want with this
method.
Just sow me the output as you think it ought to be and we can go from
there to figure out how to do it. If you want a running total, this
isn't going to do it.

Good luck.
Ira Solomon



On Sat, 22 Jul 2006 13:59:36 +0100, "chps" <chps (AT) chps (DOT) karoo.co.uk>
wrote:

Hi Ira

Thankyou for replying. I thought I had sent this email a few days ago but I
must have made a mistake somewhere.
I expected the following results in the Grand Total Column. I was really
after the final one i.e. 5.0.

Item UnitCost AmountUsed TotalCost Grand Total
A 0.1 6 0.6 0.6
B 0.2 5 1 1.6
C 0.15 10 1.5 3.1
D 0.5 1 0.5 3.6
E 0.7 2 1.4 5.0

After asking the group I eventually found a method using the Help file even
though I'd searched many times before. I used the Sum() method as the
Record Source of a textbox on the footer of a form.

Sum([Unit Cost]*[Amount Used])

As data for each Item was added the total in the textbox incremented as in
the table above.

I used the form in datasheet view as a subform. This made the textbox
invisible. To overcome the problem I placed another text box on the main
form and used the following code in the Record Source of this new textbox to
display the totals as before.

=[RawMaterials Subform].Form!TotalCost

The form works fine now just as I hoped.

Thanks for your interest. I hope my experience helps someone else.

Stuart


"Ira Solomon" <isolomon (AT) solomonltd (DOT) com> wrote