Charlotte Foust
cfoust at infostatsystems.com
Mon Feb 2 11:35:36 CST 2004
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