[AccessD] Re: DatePart Question

Gina Hoopes hoopesg at hotmail.com
Tue Feb 3 14:33:06 CST 2004


I understand your logic.  I made the assumption that it was a "one size fits 
all" data table.  Thanks for explaining it.  As to the original issue, the 
process I'm creating is an automatic one, so there is no opportunity for the 
user to choose anything.  It simply needs to take last month's data and 
process it, and I think I'll be able to accomplish that quite well with what 
I've learned in the last few days.

Thanks again,
Gina


From: "Robert L. Stewart" <rl_stewart at highstream.net>
To: accessd at databaseadvisors.com
CC: hoopesg at hotmail.com
Subject: Re: DatePart Question
Date: Tue, 03 Feb 2004 13:23:36 -0600

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




Thanks,
Gina

_________________________________________________________________
There are now three new levels of MSN Hotmail Extra Storage!  Learn more. 
http://join.msn.com/?pgmarket=en-us&page=hotmail/es2&ST=1



More information about the AccessD mailing list