Best Way to Display Data

[Pulling Hair Out]

I'm racking my brain on the best way to do this and it's time I ask the
experts. I am trying to copy a report currently used in Excel and need to
create a distributable report in Access like below.

3/15/10 MTD
1) Total Calls Forecast 100 1500
2) Total Calls Answered 95 1499
3)Care Answered 40 600
4)VRU% 95% 96%
5) Calls Aband 10 5

Number 1, 2, 5, will all come from the same query. However 3 and 4 will
come from a different query. And there will be 6 of these groupings
(relating to different sites) together vertically.

Would it be best to create reports/subreports, or is there a better way?

 

[golfinray]

For different queries or tables you will need to use subreports. REMEMBER
however that something must key each subreport to the main report. So an id
number, a case number, something must be common to them all or they will not
link.

 

[Pulling Hair Out]

Can my common "Link" be a date?

For different queries or tables you will need to use subreports. REMEMBER
however that something must key each subreport to the main report. So an id
number, a case number, something must be common to them all or they will not
link.

 

[John W. Vinson]

Can my common "Link" be a date?

If the date uniquely and unambiguously identifies a record, yes - but bear in
mind that a Date/Time value includes a time component accurate to microseconds
(displayed only to the nearest second). What's the structure and relationship
of your tables?

 

[golfinray]

No, has to be a number. Could you go in and assign a "Calls id" or an
"employee id" or anything that may be related to each? Or how about a month
id, like Jan is 1, Feb is 2, etc? Now you might use a day of the year, maybe?
1-365? Would that help any?

 

[John W. Vinson]

No, has to be a number.

That's not actually the case. You can join two tables on a number, date,
text... anything other than a Memo or OLE object.

You're right in that a number is going to be more practical in most
circumstances, but you can in fact use a date, if the date is unambiguous and
accurate.

 

[Pulling Hair Out]

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.
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".

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.
Thanks for your help.

 

[John W. Vinson]

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.