[AccessD] Using Dates

Right. My first example was an ad-hoc reporting type of thing. The second,
PO Expediting, is a nightly ETL process that reads all active POs from the
accounting system, compares it with receiving, flags late orders, and
compiles a list of everything that needs follow up.

As far as the overhead, Robert's table had about 102 bytes per date record.
Rounding that up to 8 records per kilobyte, you could fit over 22 years
worth of data in a megabyte - not a big table by most standards.

Indexing against 8 byte datetime fields adds additional overhead, but if
that was a concern, you could store and index dates as Modified Julian
Dates, which would usually require no more than a smallint.

-Ken

-----Original Message-----
From: Colby, John [mailto:JColby at dispec.com]
Sent: Friday, January 30, 2004 11:59 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Using Dates


I have to assume though that you only create these as required for
reporting?  The overhead would be enormous otherwise.

John W. Colby
The database guy


-----Original Message-----
From: Ken Ismert [mailto:KIsmert at TexasSystems.com]
Sent: Friday, January 30, 2004 12:25 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Using Dates



Gustav,

In my estimation, the simplicity and flexibility that the Date Dimension
table provides will outweigh its drawbacks for all but the most basic of
date query tasks.

For example, with a Date table, it would be possible to write a single
parameterized query that would provide answers for a wide range of date
related questions, like:
 * All January sales in the table
 * All sales for weeks with a holiday since 2000
 * All weekend sales for June, July and August of 2002
The results could easily be returned an any text or numeric date format
desired.

While you could do all of this with date calculations and the Format
function in one query, it would be very complex, and the computational
overhead would likely be prohibitive. You'd probably be forced to break it
into smaller queries, with the added maintainance that entails. Alternately,
you could build a custom WHERE clause for each question, but you've only
moved the complexity from the query to code.

It also shines when you need to calculate working day intervals, as
mentioned. I wish I had known about Date tables when I was developing a
Purchase Order Expediting module, which needed to know the number of working
days between the Order Date and the Required date. Using a properly setup
Date table would have simplified my code, and provided better results.

-Ken