As I mentioned in the Introduction, there are effective vs.
ineffective ways to create dependencies. The ineffective (or
problem) dependencies result from poor database design
decisions that open the door to potential errors. In this
section, we'll take a look at specific examples of ineffective
dependencies that typically occur in databases. As I'll
demonstrate, these ineffective dependencies can be eliminated,
yielding more consistent and accurate data.
Here are some common examples of dependent data found in
databases:
Date of Next Review -I see this one a lot. It's
common for a database to contain a field that stores a date
that is dependent on another date. For example, many
organizations require a yearly performance review. In this
case, the employee database would have a field for "date of
last review", as well as a field for "date of next review"
(which is typically one year from the date of the last
review). To carry this date of next review, however would be a
mistake, let me explain why.
When this date is stored, rather than calculated, the first
problem comes in if the "date of next review" is manually
calculated and entered by a human. A person must calculate the
next year's review date (a chance to make a mistake every time
a review is entered). If someone happens to get the original
date wrong, and later changes it, s/he has to also remember to
change the date of the next review - providing two chances to
make a mistake. With just a tad more work in the beginning,
the database can be designed with a query to calculate the
"date of next review" exactly one year in the future, based on
the "date of last review". For more information on how to do
this, search for DateAdd in Access Help.
Total Cost - This is a favorite among the accounting
crowd. They design their database with one field to record the
number of units bought or sold, and include the price per unit
in another field. The problem comes in when they record the
total cost in a third field and populate that field manually
after crunching numbers on their calculators -- a prime
opportunity to make a mistake. The dependent "total cost"
field is easily calculated by Access in a query, form or
report by multiplying the number of units by the price per
unit (simply use the * symbol for multiplication). This method
is of real benefit when, for example, the unit cost changes.
In that case, the value must be changed in only one place.
Age - I'm surprised when this one occurs, but it
does! Some databases actually have a field for the age of an
entity (e.g. person, pet, or object) which is populated
manually. With this method, the age is guaranteed to be wrong
eventually, simply because time passes. Manually updating this
data would be a never ending task! This one is also easy to
fix using the Date() function, which returns the current
system date, and then using the DateDiff function to calculate
elapsed years. Again, search for these functions in Access
Help if you would like more information.
Balance - This example is different from the
previous examples in that the value is not only dependent on
another field, but also on data that was previously entered in
the same field (much like a spreadsheet used to keep track of
a budget). Periodically I'll see a table that stores customer
invoices (money owed to a company) and customer receipts,
along with a field called Balance Due.
Usually the balance due is not entered manually simply
because the person who set it up realizes that one mistake in
the beginning would lead to mistakes in subsequent entries.
The problem arises, however, when an incorrect invoice or
receipt is corrected. Every record in the Balance Due field
after the correction, which depends on the invoice or receipt
that was corrected, must also be updated. Writing the code to
calculate or correct the Balance Due is time consuming (and
that assumes that a human actually remembers to do so).
Let's dig into this in more detail. Take a look at the
following table:
This table is designed to track receipts and invoices and
to keep a running total of the balance due. The values in the
Balance Due column are calculated manually. There are a couple
of major problems with this design. First, the balance due is
accurate only if the invoices and receipts are entered in the
correct order. Second, if someone makes a mistake in entering
data, the balance due will be incorrect for that date and for
every row that comes after it. Every balance after the
corrected row must be changed manually, one-by one (multiple
chances to make a mistake there!), or by a program coded by
the database administrator (a better solution, but time
consuming to write).
A much better approach to managing this information would
be to design a table like this:
Here both the invoices (money requested from the customer)
and receipts (money actually received) are tracked in a single
field, InvRcptAmt. Invoices are entered as positive amounts,
receipts as negative (to show a credit against the invoice).
This has the advantage of not requiring the entries to be in
any specific order and allows for easy calculation of the
balance due. The actual balance due (a value dependent on the
numbers in the InvRcptAmt column) is not shown in this table,
but it can be calculated in a query, form, or report. To do
this, you would utilize the following function:
Dsum("InvRcptAmt","tblInvRcpt","CustKey = 1")
In this case, the function will return 6, the balance due
for this Customer. I won't go into much detail about this Dsum
function since I'll be covering that in a future Wizard. At
this point, all you need to know is that it can add up a
series of numbers and give you the result.
Let me mention that this Dsum function is not the same as a
running sum. With a running sum you get a balance run for
every data row you show. The Dsum function, provides just a
single number for all of the rows you show in your report or
form.
What if you really want a running sum (balance due at each
point in time) in a report? As it turns out, Access has this
ability built in. It's a tad complex and beyond the scope of
this article; however, you can get the details by typing
"running sum" into Access Help and you'll get a detailed
explanation. If you need to get a running sum in a query or a
report, it's also a bit complex, but Microsoft has also
detailed how to do this. See http://support.microsoft.com/default.asp
x?scid=kb;en-us;290136 for details.
