[AccessD] Re: DatePart Question

DWUTKA at marlow.com DWUTKA at marlow.com
Thu Feb 5 14:34:19 CST 2004


Hey, sorry, been bogged down in a project at work, needed something to latch
onto for a break.  So I picked the wrong subject! <grin>

How about those unbound forms.....

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Thursday, February 05, 2004 12:36 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Re: DatePart Question


Good grief!  Are you sure you don't want to argue?? <VBG>

Charlotte Foust

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] 
Sent: Thursday, February 05, 2004 10:24 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Re: DatePart Question


I did some testing.  I have a table with ~ 500k records.  

Using a date table to look for a specific month is definitely faster
then looking for Month()=5

Using a date table to look for a month and year is about the same as
Between (maybe a hair slower)

Using a date table to look for a month AND another criteria within
'data' table is the same as Month() (same for Month & year)

Using date table for particular month and year AND another criteria look
a hair slower then Between.

So yes, searching strictly for dates is faster.  With an inner join
(Left join changes everything, goes at about the same speed, but can't
think of any instance where you would have a left join versus an inner
join with a date dimension table).  However, when you have criteria in
the 'data' table, the VBA method is just as fast as the date dimension
process.  

I don't have millisecond times, nor did I test it up the wazoo, but I
can understand why the date dimension table is faster now. (That was my
problem, understanding the speed difference). With an inner join, only
having criteria in the date table forces JET to look at it's indexes
first, and then only pulling up records from the indexed 'data' table.
That is fast. A Left join forces it to look at the data table first, so
there is no speed difference.  Inner join with WHERE clause for data
table negates the speed difference with the date table, because it is
only looking at the dates applied for the records found from the date
table.

So I concede on the speed issue relating to date dimensions! <grin>
(Don't get used to me conceding though....LOL.)

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Wednesday, February 04, 2004 1:23 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Re: DatePart Question


You're way off, Drew. <G>  The reason for those divisions is because
data warehouses are used to query data, period.  So the user wants to
see all fact records within a particular month for the past 5 years. The
point of a dimension table is that you don't need to do any date
calculations in the query.  All you need do is specify the month number
(no function required), and the year number.  The join filters out the
appropriate records.  You need do no date math at all to filter the
records because any date math was done when the dimension table was
created.  Since SQL is much faster than code, why would you expect this

Month([MyDateField]) = 5 And Year([MyDateField]) BetweenYear(Now())-5
And  Year(Now()) 

to be faster than this

[MonthNo]=5 And YearNo Between 5 And 10

Charlotte Foust

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] 
Sent: Wednesday, February 04, 2004 10:45 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Re: DatePart Question


I understand more 'complex' date 'info', such as holidays, fiscal
period, etc.  I still don't understand the reason for basic date
information, such as day, month, year, day of week, etc.  I have never
actually run any tests, but my gut says that a query where I wanted all
records in the month of May (ANY year), that if I put
Month([MyDateField])=5 in the Where clause, that it would be faster then
having a relationship to a date dimension table.

Data entry, or data warehousing, the speed should still be a factor,
correct? Or am I way off on my gut feeling (really too busy to build an
appropriate test.)

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Tuesday, February 03, 2004 4:38 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Re: DatePart Question


The essential difference is that the date dimension table is generally
used in data warehouse applications, not in regular data entry
databases.  They can be useful in the latter for reporting purposes
only, but you can get by nicely if you've never had to slice and dice
very large tables based on a bunch of date criteria.  Note that fact
tables in a datawarehouse are usually not normalized in the same way as
regular database tables either (they are commonly 1NF), so dimension
tables give you flexibility that it's hard to get any other way.  Data
warehousing may be slightly off-topic, but only because Access
developers don't have to deal with it very often.  I have, so I can
appreciate both sides of the discussion.

Charlotte Foust

-----Original Message-----
From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] 
Sent: Tuesday, February 03, 2004 2:05 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Re: DatePart Question


Nothing personal, but I have to agree with Gustav's point of view.  I
can see (and I believe he does too), where a table would help certain
situations.  However, I know first hand, the extreme lack of
understanding on how a date works.  I'm not saying you don't understand
that, however, to a computer, it is MUCH faster for many functions, to
just let the processor do a logic operation on a number, then to have it
pull other data up, and compare that.  Holidays, etc, those require
heavier logic, so a table could be faster (depending on the amount of
data).

I'm not knocking your approach.  But in my experience, I have never
needed to do anything like that.  

We better be careful that this doesn't turn into another bound/unbound
issue.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Robert L.
Stewart
Sent: Tuesday, February 03, 2004 1:24 PM
To: accessd at databaseadvisors.com
Cc: hoopesg at hotmail.com
Subject: [AccessD] Re: DatePart Question


Ahh Grasshopper,

And how will you learn if you do not create it?  I mean, I could send it
to 
you, or post the table to the files section, but that would not help you
or 
anyone else understand the use of the functions that went in to create 
it.  I teach an MS Access Developer's Workshop and have been doing that
for 
about 8 years.  One of the things I do in there is come up with an idea
for 
a function, process, etc. and have them as a group build it.  And about
the 
holiday and fiscal year "modifications," since I leave that up to the
user 
to define after the table is created, there are not really any 
modifications to make to the data for me.  Now, having said all of that,
I 
did send the MDB to John Colby, but that was primarily because I know he

could have created it in his sleep and because he has shared he work
rather 
freely with us and the Access development world in general.  And, it is
not 
reinventing the wheel.  It is learning how to build a wheel so you can 
build the cart that goes with the wheel.  ;-)  Besides, you may build it

quite differently from the way I do it or the way John would do it.

Here is another example of "thinking outside of the box."  How do you do

entry for addresses?  Well, I use a table of Zip codes.  From that, I
get 
the city and state.  So they enter the zip code first and then they may 
have to select the correct city but the state will always be correct.  I

use a data-limited combobox for the city so that, for example, they can 
select one of the 4 "towns" that use 77418 (my zip code).  I also
default 
each of the limited comboboxes to the first value in their list, so if 
there is only one city (77002, Houston) in the list, they do not have to
do 
anything else, thus saving many keystrokes.

Actually, you would get both Jan 2004 and Jan 2003, etc. data because it
is 
looking at only the month with what you were looking at doing.  What I
do 
is give the user a form for selecting a beginning month, beginning year,

ending month, and ending year for reports.  If they leave the month off,
I 
only look at the year.  If they only give the beginning month and year,
I 
do a year-to-date.  And so on.  Since I dynamically build the where
clause 
of the SQL statement, I just implement it as a business rule and explain
to 
the users how their entries will power the criteria for their reports.

The end result is that the Date Dimension table will give you a lot of 
control and flexibility in being able to gather criteria and validating
it.

Robert

At 03:46 AM 2/3/2004 -0600, you wrote:
>Date: Mon, 02 Feb 2004 12:12:19 -0600
>From: "Gina Hoopes" <hoopesg at hotmail.com>
>Subject: [AccessD] Re: DatePart Question
>To: accessd at databaseadvisors.com
>Message-ID: <Sea1-F130hhDpB8ZdL300037f77 at hotmail.com>
>Content-Type: text/plain; format=flowed
>
>Thanks to you and to Gustav for giving me more good ideas.  I guess 
>your question kind of goes back to my original one.  If it looks at the

>whole date before giving me the "1" for the month, then I'll get Jan 05

>data rather than Jan 04 data.  I guess the question is senseless since 
>both Joe and Gustav have given me good alternatives, but I may still 
>have to plug some fake data just to see what I get back.
>
>I have been following the dates table discussion and I've got another
>(stupid) question.  Except for fiscal years and holiday schedule 
>modifications, is this not the type of table that one of the people who

>has already spent the time to create it could just put out for everyone

>to use? I'm afraid I started following the discussion a bit late, so 
>maybe I missed something.  It's not that I mind a day spent creating 
>something really useful, it just seems a bit like re-inventing the 
>wheel.
>
>Gina


_______________________________________________
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
_______________________________________________
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