[AccessD] RE: Using Dates

Colby, John JColby at dispec.com
Mon Feb 2 11:57:19 CST 2004


Charlotte,

>The calendar date table is for dates, not time, although I suppose it could
be extended if you were into pain.

Yes, but many people use a single field to hold a date / time, which
wouldn't index cleanly into the date table.  The time would have to be
stripped off before the join.

John W. Colby
The database guy


-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Monday, February 02, 2004 12:36 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] RE: Using Dates


The PK of the dimension table is the date itself.  I'm not sure what you
mean about the meaningless pk in this regard.  You could use an
autonumber and make the date field a unique key, but I don't know what
that would buy you.  You wouldn't ever join a date time like that.  The
calendar date table is for dates, not time, although I suppose it could
be extended if you were into pain.  This kind of table is typically used
where the user may want to slice and dice data on the fly.  In that
case, the date fields in the data tables are already indexed for that
purpose. 

Charlotte Foust

-----Original Message-----
From: Colby, John [mailto:JColby at dispec.com] 
Sent: Monday, February 02, 2004 9:19 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] RE: Using Dates


lol.  All I needed to know is that you join any date (use the date as
the PK in the date table).

I notice that the proponent whazizname doesn't recommend this though,
rather using a "meaningless" pk (autonumber?).  When it comes back to
this it becomes less obvious how to use it.  

I can easily build a date() as a default value for a field for example.
However having to lookup that date and get the PK of the record matching
that date... a little less obvious.

I will no doubt look at this though.  I can see just thinking about it
that it isn't the end all / be all, even speed wise.  For queries that
only return 1 or a handful of records, using direct functions to build
some piece of the date would be MUCH faster than doing a join on this
table, pulling the index for 10,000 date records etc.  And there is
always the issue of joining a date directly when it has a time component
- now() instead of date().

Interesting none the less.

John W. Colby
The database guy


-----Original Message-----
From: Charlotte Foust [mailto:cfoust at infostatsystems.com]
Sent: Monday, February 02, 2004 11:57 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] RE: Using Dates


Ah, a convert! <VBG>

Charlottet Foust

-----Original Message-----
From: Colby, John [mailto:JColby at dispec.com] 
Sent: Friday, January 30, 2004 11:06 AM
To: 'Robert L. Stewart'; accessd at databaseadvisors.com
Cc: Colby, John
Subject: [AccessD] RE: Using Dates


I think I finally "got it".  This is joined to any date to pull out the
various pieces needed?  Then you build a function to populate this thing
for the dates that your app needs?  

I think I might just appreciate this one!  ;-)

John W. Colby
The database guy


-----Original Message-----
From: Robert L. Stewart [mailto:rl_stewart at highstream.net]
Sent: Friday, January 30, 2004 1:58 PM
To: accessd at databaseadvisors.com
Cc: JColby at dispec.com
Subject: Re: Using Dates


Actually, you have a "standard" date dimension table that everyone uses
and 
the overhead is minimal.

At 12:00 PM 1/30/2004 -0600, you wrote:
>Date: Fri, 30 Jan 2004 12:58:44 -0500
>From: "Colby, John" <JColby at dispec.com>
>Subject: RE: [AccessD] Using Dates
>To: "'Access Developers discussion and problem solving'"
>         <accessd at databaseadvisors.com>
>Message-ID: <05C61C52D7CAD211A7830008C7DF6F1079BD12 at DISABILITYINS01>
>Content-Type: text/plain;       charset="iso-8859-1"
>
>I have to assume though that you only create these as required for
>reporting?  The overhead would be enormous otherwise.
>
>John W. Colby


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


More information about the AccessD mailing list