Calculate a Percentage field based on another calculated field

[JUAN]

Hello there,
I have a query, which I added a Field that would calculate
the sum of some of my fields. Now what I would like to do
is do a percentage field base on the calculated field and
one of my fields. Here's my calculated field:
Total # with Codes: Nz([01])+Nz([02])+Nz([03])+Nz([04])+Nz
([05])+Nz([06])+Nz([07])+Nz([08])+Nz([09])+Nz([10])+Nz
([11])+Nz([12])

Here's one of my fields:
Total # of lines

So I want a new field
Percentage of lines:Total # with Codes/Total # of lines
This doesn't work so not sure if its possible to do
another calculated field, or If I need to create another
query to do this.
Please advise if I'm not clear. Any help is very
appreciated.
Thanks,
Juan

 

[Ken Snell]

You need to repeat the calculation in the new calculated field, and you need
to put the other field name inside [ ] because the name contains a space:

Percentage of lines:
(Nz([01])+Nz([02])+Nz([03])+Nz([04])+Nz([05])+Nz([06])+Nz([07])+Nz([08])+Nz(
[09])+Nz([10])+Nz([11])+Nz([12]) / [Total # of lines]

 

[Jeff Boyce]

Juan

Based on your formula, I'm guessing that your table structure could use a
little normalization. What kinds of data do you have in your fields named
[01], [02], ...? (monthly amounts, perhaps?!).

One problem with embedding meaning in repeating fields (like one field for
each month) is that you can only handle one year before needing to add still
more fields, add a new table, and/or modify every query, form, report and
code module that refers to the data.

With normalization, you'd have one field to hold the amount, and one field
to hold the date (notice, not the Month, but a date). With a design like
this, you can calculate an average just by specifying the date range of
values to include in the Totals query that uses Average().

Good luck!

Jeff Boyce
<Access MVP>

 

[JUAN]

Hello Ken/Jeff
thanks for the info. I'm able to get what I want. once
again thanks for taking the time.

JUAN