Chapter 16

Express Yourself with Formulas


check Developing an expression

check Performing complex calculations

check Calculating text fields

check Using Expression Builder

Efficient database design requires that tables contain only necessary fields. Too many fields can cause a table to load slowly — you won’t notice the difference with a few hundred records, but you certainly will with a few hundred thousand. So what fields are often added unnecessarily to a table’s design? The short answer: fields that could be generated from calculations on data stored in other fields.

For example, suppose you have a Commitment table with a DonationCommitment field and a Donation table with a DonationAmount field. You may be tempted to add an AmountOwed field to your Donation table that stores the result of subtracting DonationAmount from DonationCommitment. This is unnecessary because Access can perform these calculations on the fly — in what's called a Calculated field.

A Calculated field takes information from another field or fields in the database and performs some arithmetic to come up with new ...

Get Access 2019 For Dummies now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.