Re: Newbie question on formatting a field in table design

"albin" <prime_nurex@xxxxxxxxx> wrote in


{Background setting}
I have what I think is a simple question that evades me since
I just started using Access. I have a table that has 10
fields that distribute utility costs to 10 departments (Depart
A through Dept J) but only if the department was active for
the month. Each department field is set to the default format
for percent, which according to the Access help menu
multiplies the number entered by 100 and adds the "%" sign.
{Problem 1}
Unfortunately, this becomes a headache for the users when
entering the data. For example if I want to enter 20%, I type
in "20" into the field which Access then multiplies by 100 and
adds a "%" at the end which results in an incorrect amount of
"200.00%". This means that the entry number should be in
decimal form; however, people in the department are not
comfortable working with decimal forms. Is there a way to
customize the percent format not to multiply by 100 and retain
the data as a percentage?
{Problem 2}
Since we are using 10 departments for each branch office to
allocate costs, I have to assure that each branch office has
100% of its costs allocated. Is it possible to use a
validation rule for a field to assure that the total for all
departments equals 100%? Would I need to create a field in
table design to add up the department or is that only possible
through a query?

P.S. I am very new to Access so any help would be greatly
appreciated. I hope I made myself clear on the questions.
Thanks in advance.

Where to begin?.

You have posed your questions very concisely.

Firstly, one thing every newbie hasto learn is to never let the
users touch the tables directly. Data entry should be done via a
form. A well planned makes a much more practical user interface.

In the form you could set up the textboxes for your values to
take an integer value, and display the percent as a label to the
right of the textbox, then store the number that way and do the
same in any reports, or you could trigger the textbox
AfterUpdate event to divide by 100, and store the fraction in
your table. I only worry about formatting at teh point of use,
never at the table level. This gives me so much more

You can also set the forms beforeUpdate property to validate
that your distribution totals one, or, which you cannot do in a
table is to prorate the entered values so that they do equal 1.
What I mean is that it may make sense to enter the three working
departments as 2,1,1 instead of 50,25,25, and have the form
calculate the actual percentage distributions.
Another point: normalized table design would convert your 11
fields per row to a 3 fields per 10 rows, so that you would have
Month, department, percentage, instead of
month, Department_A_percentage,Department_B_percentage, etc.

This is because the 11 row design would need changes to every
form, report and query when the company decides that 12
departments is better than 10.

Bob Quintal

PA is y I've altered my email address.

Posted via a free Usenet account from