Add new column to summarize data

[accessfever]

I have a table to compare current sales unit price and forecast sales unit price for some sales inventory by model number. See below for an example.

Model current future variance
B-type $10 $20 $10
C-type $20 $18 -$ 2
D-type $30 $60 $30
E-type $150 $200 $50

What I want to do here is to create a selete query to add a new column to summarize the variance by a range. See below for an example if I want to have summarize the variance by an increment of absolute value of $20. The table has more than 300,000 records. I don't want to insert a column in the table.


Model current future variance var_range
B-type $10 $20 $10 =$20
C-type $20 $18 -$ 2 =$20
D-type $30 $60 $30 =$40
E-type $150 $200 $50 =$50

Any idea?

 

[dkinley]

To add a column in the query simply go to an empty column in the QBE and type a variant name. Say for instance "Var_Summ". Then perform your calculation after inserting a column ...

Var_Summ: [column_n] + [column_n+1] + ...

On the ribbon bar, you should see the summation character sigma. Click it and a Total row will show. You should be able to hit the drop-down box on the column you added and select "Var" which is the estimated variance.

HTH,
-dK