This database reports on call center metrics. There are several tables which
come together for a Daily, MTD, YTD total/avg of call center measurements.
Well... in general, totals and averages should NOT be stored in any table, but
should instead be calculated on the fly. Or am I misunderstanding?
Simply put, one table would have info on "Adherence". Fields would be Date,
Queue, ADH %. Another table would have call data (Date, Queue, # answered,
Avg Handle Time, etc). Common fields would be Date and "Queue".
Even this table appears to contain averages. Does a *single call* have an
"average handle time"? or are you consolidating raw data from multiple calls
to calculate the average on the fly? Where is the underlying data?
I understand what you're saying about the "Date" field being Date/Time. It
may be better for me to add a number between 1-365 for the primary key.
Certainly NOT!!!!! unless you want to have the database crash and burn on
January 1.
One thing you should certainly do is rename the Date field. Date is a reserved
word, for the builtin Date() function which reads the computer clock; Access
will get confused. I'd rename this field Calldate.
If the field is populated with just pure date values - i.e. using Date()
rather than Now(), and having no stored time portion, then yes, you can use it
as a linking field between tables. You can join table son two fields (or ten
fields for that matter) - you could create a query, add your two tables, join
one table's Queue field to the other table's Queue, and then join the two Date
(or CallDate!) fields.
But I'm still concerned about the appearance that you're storing derived data.
Here's my spiel on that issue:
Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.
Just redo the calculation whenever you need it, either as a
calculated field in a Query or in the control source of a Form or a Report
textbox.
If you have very large numbers of calls (thousands per day) and *DEMONSTRATED*
(not assumed) inadequate performance, then you might make a case for storing
averages.