Access

[chase]

I want to divide the data in one column by the data in
another column and have the results posted in another
column. Think it must be done in the design view but
have not been able to figure out the exact steps to make
it happen.

Any assistance would be appreciated.

 

[Allen Browne]

You don't do this in a table.
You do it in a query.

Create a query into your table.
In a fresh column, field row, enter:
C: IIf(=0, 0, [A]/)
where A is the numerator field, B is the demominator field, and C is the
name of the result column.

One of the basic rules of data normalization is that you never *store* a
result that is directly dependent on other data.

 

[Jeff Boyce]

Chase

As Allen points out, good relational db design avoids storing calculated
values.

What you describe is typically done in a spreadsheet -- do you need to be
using a spreadsheet instead of Access?

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Thanks, Allen. I am still stuck. Is the 2nd "I" below
in "IIf" a typo? I substituted the field values for C, A
and B and put the query in the 1st column and the first
row. No results appear when I look at my table. Should
I be using more than one column for this query? And, do
I put anything in the "table" row below the field row?
Sorry, I am such a dummy when I comes to using the right
syntax. If I can learn to do one, it will not be so hard
to do the next calculation.
Thanks for your help.

-----Original Message-----
You don't do this in a table.
You do it in a query.

Create a query into your table.
In a fresh column, field row, enter:
C: IIf(=0, 0, [A]/)
where A is the numerator field, B is the demominator field, and C is the
name of the result column.

One of the basic rules of data normalization is that you never *store* a
result that is directly dependent on other data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to divide the data in one column by the data in
another column and have the results posted in another
column. Think it must be done in the design view but
have not been able to figure out the exact steps to make
it happen.

Any assistance would be appreciated.

.

 

[Allen Browne]

IIf() stands for Immediate If.
It consists of 3 parts, separated by commas:
- something that is either true or false, e.g. =0;
- the answer to use if the first expression is true;
- the answer to use if the first expression is false.
It is used here to avoid division by zero.

So, if you have fields named "SampleTotal" and "NumberInSample", the Field
row in the query would be:
AverageScore: IIf([NumberInSample]=0, 0, [SampleTotal] / [NumberInSample])

It all happens in the query: you do not need anything in the table to store
the average score.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks, Allen. I am still stuck. Is the 2nd "I" below
in "IIf" a typo? I substituted the field values for C, A
and B and put the query in the 1st column and the first
row. No results appear when I look at my table. Should
I be using more than one column for this query? And, do
I put anything in the "table" row below the field row?
Sorry, I am such a dummy when I comes to using the right
syntax. If I can learn to do one, it will not be so hard
to do the next calculation.
Thanks for your help.

-----Original Message-----
You don't do this in a table.
You do it in a query.

Create a query into your table.
In a fresh column, field row, enter:
C: IIf(=0, 0, [A]/)
where A is the numerator field, B is the demominator field, and C is the
name of the result column.

One of the basic rules of data normalization is that you never *store* a
result that is directly dependent on other data.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to divide the data in one column by the data in
another column and have the results posted in another
column. Think it must be done in the design view but
have not been able to figure out the exact steps to make
it happen.

Any assistance would be appreciated.

.