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


Relevant Pages

  • Re: Use TextBox Text As Function Name!
    ... the text in the TextBox changes to "<the name of the ... > OptionButton that was checked just before the selection change>2<the ... You have some design issues here. ... using the most common format. ...
  • Re: Using VBA need to add a text box to a form
    ... Form tblExcel_Check must be open in design view. ... The form must have a textbox named txtCurrent, ... Another way to get the result you are after is to manually add the format ... Allen Browne - Microsoft MVP. ...
  • Re: african date
    ... Open the Form in design view. ... Mouse click on the textbox. ... In the properties window click on the Format tab. ... John W. Vinson [MVP] ...
  • [OT]? SQL or Program Code?
    ... The present design and implementation therefore possess several ... the number format should be processed. ... DBMS level at all. ... stopping them from doing this themselves in their controller and then ...
  • Re: OT: Ghostscript, wont install
    ... Client said to install Ghostscript. ... That format is IMHO the pits. ... schematics in that format. ... Obviously I design chips for a living... ...