Microsoft Office Tutorials and References
In Depth Information
Storing Single Facts
✦ Percentages: To store percentages, such as discounts, create a Number
field, and enter decimal numbers between 0 and 1 (inclusive) for
percentages between 0 and 100. When you create the table, you can format
the Number field as a percentage. Then, if you enter a value, and habit
makes you type 33% , Access automatically converts the value to 0.33.
✦ Calculations: Access 2013 includes a Calculated field type, which stores
the results of calculations that use other fields in the same table. Official
relational database theory says that fields should contain only raw data;
Access can always do the calculations later in your queries, forms, and
reports. The space required to store a number is pretty small, however,
and if you’ll need to use a calculation in lots of forms and reports, it’s
convenient to enter the formula just once as part of the table definition,
so we say go ahead! You may want to enter a Total_Price field in the
Order_Details table that multiplies Quantity by Price_Each, because
this number is likely to show up on invoices, receipts, and sales reports.
Never create a field in which you enter the result of a calculation
yourself. If the numbers on which the calculation was based happen to
change, the calculation becomes wrong, which fouls up any calculations
or reports based on it. An Access Calculated field updates the result
automatically when other fields change.
✦ Codes: Decide on the formats to use for phone numbers, invoice
numbers, credit-card numbers, purchase order numbers, and other codes.
Decide whether to use all capital letters and whether to include or omit
dashes and spaces. If you ask Access to search for someone with
creditcard number 9999–8888–7777–6666, and the card number is stored as
9999888877776666, the search won’t find the record.
Storing Single Facts
Some pieces of information exist all by themselves. They aren’t part of a
list; there’s just one item. The name of your organization is a single piece of
information, and so is the pathname to the location of your database. If you
want these pieces of information to appear on any reports, forms, or
queries, or to use them in calculations or imports, typing them willy-nilly in said
reports, queries, or other Access objects is tempting. In practice, doing so
turns out to be a lousy idea.
Here’s the problem: What happens when one of these facts changes?
Suppose that your organization’s name or address changes, or you move
your database’s location to another folder on another computer. You
certainly don’t want to have to root around your database looking for the
places where such information appears.