Newbie question on formatting a field in table design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

albin - 30 Jun 2006 19:51 GMT

Hello,

{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.

Reply to this Message

Bob Quintal - 30 Jun 2006 21:40 GMT

> Hello,
>
[quoted text clipped - 28 lines]
> 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
flexibility.

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.

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message