Multiply Fields, Using The Sum Function.

[BlankReg]

Hi all, wondering if anyone can help me.

Im trying to a run a query to see the results of my table. but i am experincing problems, when use the SUM function.

I have constructed a query with three fields.
1) QTY.
2) Cost
3) Total.

what i would like is to be able to Multiply QTY & Cost to get the total.

so whan QTY Is 0 and Cost is £0.00. the query recongises it and changes the total should then be £0.00.

any advice on enabling the query is welcome.

 

[neileg]

The total should be a calculated field in the query [QTY]*[Cost]. This will work just fine with zero values.

I wonder, though, if you are having problems with null values because these will produce an error. In that case change the calculation to Nz([QTY])*Nz([Cost]).

Nz() is a built in function that converts nulls to zero (or indeed other value if you specify the value).

You don't need to use SUM at all.

 

[jzwp22]

If you want to find the product of the QTY and cost for each record and both the cost and QTY fields are in the same table, you would not need to use SUM, just an expression. I assume you might have some identifier such as an ItemNumber or ProductNumber that the cost and Qty are tied to. And the items might be tied to an order of some sort

query name: qryLineTotal
SELECT orderID, itemnumber,cost, QTY, (cost*qty) as LineTotal
FROM yourtablename


Now if you wanted to find the total of items by order#, then you would use SUM

SELECT orderID SUM(LineTotal)
FROM qryLineTotal
GROUP BY orderID

I'm not sure if this is what you are after, but perhaps it will point you towards a solution.

 

[BlankReg]

Hi There, Thank you for your reply. forgive me for my stupidness, but how would i write the above into my query.

which criteria field would i insert Nz([QTY])*Nz([Cost]). into?

Thank you.

 

[Brianwarnock]

Nz([QTY])*Nz([Cost]) is a calculated field not a criteria, you enter it into the field row
Total: Nz([QTY])*Nz([Cost])

Brian

 

[BlankReg]

Hi All. i finally have it working and it looks great. one tiny problem, is there a way that i could add a pound sign to this Total: Nz([QTY])*Nz([Cost])
so when the query runs and brings up the results, they all have (£0.00) infront of them?

thanks for your help

 

[Brianwarnock]

Is total a field in your table?
Are you infact trying to do an update?

Brian

 

[BlankReg]

yes total is a field in my table. as the QTY changes, the Total will need to change.

thanks

 

[Brianwarnock]

You should not store a calculated field in your table except under special circumstances, and this does not appear to be one, as it can cause data integrity problems. You can always calculate this field when required eg in a query, form or report.

Brian

 

[jzwp22]

I agree with Brian, calculated values should not be stored. You had an additional question about the currency symbol. In order to show this, go to the design grid for the query and highlight the total column. Right click and go to properties and select the appropriate format.

 

[BlankReg]

Hi Again. Thank you for all your replies. the query works great!!

one final thing, i have 3 forms, with parameter queries, so when run it asks you to enter a part no, description or a component. that all works great.

i was wondering, would it be possible to create a form based on the query i just constructed. so then when the form is opened they would be asked to enter the part no, then see the QTY, Cost and the Total.

Thanks once again.

 

[jzwp22]

Yes, you can create a form based on your parameter query. If you are using the form wizard it will bring up a window to select a table or a query. Just select your parameter query & then when the form is opened it will prompt the user for the parameters to enter.

 

[BlankReg]

Thanks everyone for your help.

the database works a treat