Duplicate Issue

[#1]

I have joined some tables together and notice that i have duplicates: i need to be able to somehow compress these values to only show the instance happening once (because it did not actually occur 3 times). The answer should say 123456 = 75, not 225 which is what it is currently showing me.

Any thoughts?

Item#          Final Fcst          Qty

123456          75                    4

123456          75                    6

123456          75                    13

Thanks,

Parrish

[Gysbert_Wassenaar]

Try using sum(distinct [Final Fcst]) instead of sum([Final Fcst])

[#3]

I would stick to average in this case

avg([Final Fcst])

[kouroshkarimi]

I'm assuming the Qty field is causing the table to segregate like that. If that table is in the front end then change Qty to sum(Qty).

If it's in the script, then you'll need to introduce a group by clause. e.g.

table a:

load [Item],

     [Final Fcst],

     sum(Qty)

resident mytable

group by

      [Item], 

     [Final Fcst]

;

[#5]

Hi,

Try this

(Answer You get)/Count( [Final Fcst])