total excluding duplicate

[mithani]

Dear Friends,

I have one query based on code, rate, quantity. I created field for total (rate x quantity). What expression i can write that the query will calculate total only with the first figure as per code and if code repeats again, should not calculation total.

Please see the following query structure. Thats how i want my total to be calculated.

Code, Rate, Quantity, total
50,100,10,1000
50,100,10
60,200,20,4000
60,200,20
60,200,20
70,200,30,6000

Thanks
mithan

 

[KeithG]

Try one of the two below SQl

Select Distinct Row Code, Rate, Quantity, rate x quantity As Total

or

Select Distinct Code, Rate, Quantity, rate x quantity As Total

 

[mithani]

Thanks KeithG,

Where I have to write this select statement in my query?

Thanks

mithani

 

[mithani]

KeithG,

My query should show all record(even duplicate) but only while doing total (quantity * rate), i don't want to calculate total for repeated code. So i think i have write something in criteria?

Please help me.

Mithani

 

[KeithG]

I don't think you are going to able to achieve this. Adding critiria will narrow the record shown. Why do you want to show duplicates?

 

[mithani]

Thanks KeithG,

My query is based on two tables, one is purchase and other is just project expenses code. Many purchases transactions can be done in each expenses code. I know I have a problem with table nomalization and my table structure has to be changed but didn't get much time to do that.

Is it possible while making my report, I can restrict in my total not to calculate when expenses codes repeats?

thanks for your help

mithani

 

[mithani]

Friends,

Is it possible while making my report, I can restrict in my total not to calculate(quantity * rate) when "expenses_codes" repeats?

Appreciate your help

mithani

 

[ajetrumpet]

Friends,

Is it possible while making my report, I can restrict in my total not to calculate(quantity * rate) when "expenses_codes" repeats?

mithani

No, not if your report is based off of a query. The closest you can get is writing an IIF statement for one of the fields, but that won't do it for you, because it can't look at the previous record to get a value. However, you can do it in visual basic...