If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Holiday Table Design
I thought the article explained how the details for each holiday was stored
in the table. Family Day falls on the third Monday in February. That means you'd set FixedMonth to 2 (for February), FixedWeekday to 2 (for Monday) and FixedWeekdayofMonth to 3 (for the third Monday) -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Billiam" wrote in message ... Hi Douglas, Thank you for responding! The Holiday calendar is awesome...thank you for passing it on to me. How do I add new Holidays, for example, like Family Day, in Canada (observed in my Province of Ontario)... Is there a way to take the values calculated and have them added to the holiday table for reference by another function? I suspect a command button on click function to append the results to the table? I have never done this before...any advice? Or perhaps you have a better suggestion? Thank you again for your help! Billiam "Douglas J. Steele" wrote: Check my June, 2005 "Access Answers" column in Pinnacle Publication's "Smart Access". You can download the column (and sample database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Billiam" wrote in message news Thank you for responding, Klatuu! So all the dates would be in the same Holiday table? Do you know if anyone has written a routine to autopopulate Holidays of a fixed day? I suppose I could just enter the list in excel of those Holidays whoose day does not change, just the year, and autofil them for a decade or so and then imoprt them to the Holiday table...does this seem reasonable? Thanks again for your help! It was fortunate that you mentioned the Electric Company scenario, as I had forgotten there are 2 occassions when we have to work Saturday, which is not a normal work day... Thank you again for your continued help! I would have thrown Access out without this forum, and very kind and knowledgable volunteers. Thanks for shining a light for those of us stumbling around in the dark! For me, Access is like a bad addiction, you know it's bad for your health (for me frustration and stress) but the results are SO WORTH IT, if you can design what you need... Best Regards, Billiam "Klatuu" wrote: Entering each holiday once a year is the normal way of doing it; however, if your calculations have to reach beyond the end of the current year, then you would need to load them ahead of time. Certainly it is a pain to do so, so you might try writing a routine that will autopopulate those that never change days or the day can be calculated and enter the rest by hand. But normally, I would not load holidays that would not be used at the local level. But then, if your application needs to consider the foreign holidays, you would need them. -- Dave Hargis, Microsoft Access MVP "Billiam" wrote: Hi John, Thank you for responding! The WorkDay field is also handy if the particular holiday is not celebrated in your particular area. For example, in Canada, certain holidays, such as Family Day, or St. Jean-Baptiste Day, are only celebrated in certain Provinces, or, for that matter, take Thanksgiving which is celebrated at a different date in Canada and the USA...I think it gives a bit more flexibility to the design having this field...but, what do I know! What is confusing to me is how this holiday table is updated each year, John...do I have 10 entries for New Year's Day for the next 10 years in the same Holiday table, or do I have a 2010 holiday table, a 2011 holiday table? This is probably nonsense since I am very unskilled in Access, but trying to become less unskilled,LOL, but I have this sense that I would have a holiday table with holiday name that I would link with a query possibly to the t_DecadeDates... Maybe you could explain the most efficient way to produce a list of holidays each year...this may be my bind spot, as I only see entering them one by one each year...this seems silly for days that never change, like New Year's Day and Christmas, however, Easter, and holidays which are calculated, it would seem there should be some query/programming to be able to produce that list without too much trouble? But then again, it's not like I am just stumbling over them on the internet, which makes me think it's not so easy afterall? As always, I would be aprreciative for your guidance on this! Best Regards, Billiam I have produced a table of dates for the next 10 years (an imported table from excel). "John W. Vinson" wrote: On Sat, 3 Oct 2009 12:57:01 -0700, Billiam wrote: I am working on a making a timesheet db in Access 2007 for our non-profit organization. Advice has been given many times, and many ways on calculating the difference between two dates, and, excluding holidays. I started with a holiday table like that proposed in the Access wizard may 2007. holiday key, PK autonumber Holiday text description HolidayDate, Date/Time WorkDay, Yes/No Is this really the best way to design a holiday table? it seems like i am breaking normalization rules by adding a different version date of Easter in the same table based on the year? Any advice on the best design for a holiday table would be appreciated! Thanks, Billiam It looks good to me, and I don't see why that would be in the least denormalized. Easter 2008 is a different day than Easter 2009. My only quibble would be the WorkDay field; it seems overly elaborate, since you could very reasonably consider that the existance of a record for a given date would mark it as a holiday just by it being in the table. I guess this could be useful if you wanted to wish users a "Happy Groundhog Day" even if that day wasn't observed as a day off. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|