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 |
#11
|
|||
|
|||
Holiday Table Design
On Sun, 4 Oct 2009 10:02:01 -0700, Klatuu
wrote: Actually, John, I did this once for a purpose. It was for an Electic energy company. Saturdays were typically Non Working days, but about 3 or 4 times a year, depending on a weird meter reading cycle, Saturday became a work day and those days had to be used in the calculation. Good point! It would certainly give more flexibility. -- John W. Vinson [MVP] |
#12
|
|||
|
|||
Holiday Table Design
On Sun, 4 Oct 2009 10:40:01 -0700, Billiam
wrote: 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? Autofill in Excel followed by copy and paste would be even faster than writing a query or code to do so. What's trickier than the 4th of July or other fixed-date holidays are the "first Monday in September" or "Fourth Thursday in November" type things. I could spend fifteen minutes writing and testing code to do this... or ten minutes with the Outlook calendar in one window and an Access table or form in another, filling it in by hand! Remember, if you fill out ten years' of holidays, it's probably a one-time operation and who knows what technology will be around in 2019 to take it from there. 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... Yup. That's why we all love (and hate) it so much...! -- John W. Vinson [MVP] |
#13
|
|||
|
|||
Holiday Table Design - Stevie is really pimping
"Steve" wrote in message
... Hello Billiam, I would like to offer to design and populate a holiday table for you. I provide help with Access, Excel and Word applications for a small fee. My fee to help you would be very modest. If you are interested, contact me. Steve So what is it now, you are back from hiding and you are up to six pimping posts and nothing that qualifies as free help? These newsgroups are provided by Microsoft for FREE peer to peer support. There are many highly qualified individuals who gladly help for free. Stevie is not one of them, but he is the only one who just does not get the idea of "FREE" support. He offers questionable results at unreasonable prices. If he was any good, the "thousands" of people he claims to have helped would be flooding him with work, but there appears to be a continuous drought and he needs to constantly grovel for work. A few gems gleaned from the Word New User newsgroup over the past Christmas period and a few gems from the Access newsgroups to show Stevie's "expertise". Dec 17, 2008 7:47 pm Word 2007 .......... In older versions of Word you could highlght some text then go to Format - Change Case and change the case of the hoghloghted text. Is this still available in Word 2007? Where? Thanks! Steve Dec 22, 2008 8:22 pm I am designing a series of paystubs for a client. I start in landscape and draw a table then add columns and rows to setup labels and their corresponding value. This all works fine. After a landscape version is completed, I next need to design a portrait version. Rather than strating from scratch, I'd like to be able to cut and paste from the landscape version and design the portrait version. Steve Dec 24, 2008, 1:12 PM How do you protect the document for filling in forms? Steve One of my favourites: Dec 30, 2008 8:07 PM - a reply to stevie (The original poster asked how to sort a list and stevie offered to create the OP an Access database) Steve wrote: Yes, you are right but a database is the correct tool to use not a spreadsheet. Not at all. If it's just a simple list then a spreadsheet is perfectly adequate... Sept 10, 2009 (In respose to a perfectly adequate GENERIC solution stevie wrote) This function is specific to the example but not generic for any amount paid out. Steve Sept 9, 2009 "Steve" wrote in message you can then return all the characters in front of it with the Left() fumction. Would look like: Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1) Steve No, it would not look like Left("YourString",Instr("YourString","VbCr" Or "VbLf") - 1) First of all, the constants are vbCr and vbLf: no quotes around them. With the quotes, you're looking for the literal strings. Second, you can't Or together character constants like that. Even if you could, Or'ing them together in the InStr function like that makes no sense at all. Sept 22,2009 Sorry Steve, even I can see that this is a useless answer. I made it pretty clear that "CW259" is just ONE possible value for the control. "Steve" wrote: Hello David, Open your report in design view and select txtOrderID. Open properties and go to the Data tab. Put the following expression in the Control Source property: =IIF([chkActive],"CW259","(CW259)") Steve John... Visio MVP |
#14
|
|||
|
|||
Holiday Table Design
"Steve" wrote in message
... Hello Billiam, Get lost $teve. Go away... far away.... No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html (Website has been updated and has a new 'look'... very soon we will 'celebrate' 10.000 pageloads...) For those who don't 'agree' with this mail , because $teve was 'helpfull' with his post... We warned him a thousand times... Sad, but he is not willing to stop advertising... He is just toying with these groups... advertising like hell... on and on... for years... oh yes... and sometimes he answers questions... indeed... and sometimes good souls here give him credit for that... == We are totally 'finished' with $teve now... == Killfile 'StopThisAdvertising' and you won't see these mails.... |
#15
|
|||
|
|||
Holiday Table Design
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] |
#16
|
|||
|
|||
Holiday Table Design
Hi Billiam
FWIW, this is what *I* do... Holidays are either: 1. On a set day (e.g. Christmas Day) 2. On the closest given day of the week to the set date (e.g. closest Monday to 25 Feb) 3. On a day relative to Easter (e.g. Good Friday, Easter Monday) 4. On the nth (or nth-to-last) day-of-week of the month (e.g. second Monday in June, last Thursday in November) Here is a function that I use to calculate holiday dates given those requirements: =============== start code ======================= Public Function CalcHoliday(y As Integer, _ m As Integer, d As Integer, nType As Integer, _ Optional closest As Integer) As Date Select Case nType Case 0 ' holiday is on the actual day of the year ' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 ) CalcHoliday = DateSerial(y, m, d) Case 6, 7, 8 ' holiday is on the weekday (c) closest to (before ' or after) the actual day of the year ' n=6:closest before, 7:closest after, 8:either way ' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 ) CalcHoliday = ClosestWeekdayTo( _ DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0)) Case 9 ' holiday is d days before/after Easter Sunday (m is ignored) ' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 ) CalcHoliday = EasterDate(y) + d Case Else ' holiday is on the nth WeekDay (d) of the month (m) ' (1=Sunday, 2=Monday, etc) ' if n is negative, then the nth-to-last ' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 ) CalcHoliday = NthWeekdayInMonth(y, m, d, nType) End Select End Function ================= end code ====================== It will calculate the date of a holiday in any year, using a method that depends upon the value of the third argument (nType) If nType is: 0 : holiday is on the given date (y/m/d) 6, 7, 8 : holiday is on the closest weekday (closest) to the given date (6 :closest before, 7:closest after, 8:closest either way 9 : holiday is d days relative to Easter +(1..4) : holiday is nth weekday (d) in month -(1..4) : holiday is nth-to-last weekday (d) in month It uses three other functions (see further down below). OK, now as well as having a Holidays table, as you describe, I have a table named PerennialHolidays with these fields: HolName: text, PK (e.g. "Christmas Day") HolMonth: integer HolDay: integer HolType: a number indicating the type of calculation (see nType above) HolClosestDay: optional integer HolOrder: numeric (just to keep them in order for maintenance) Records might look like this: Christmas Day, 12, 25, 0 Easter Monday, 0, 1, 9 (one day after Easter Sunday) Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov) Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May) etc. Now, to add all the holidays for a given year, one needs only run an append query, like this: Insert into Holidays (HolidayName, HolidayDate) select HolName, CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) ) from PerennialHolidays where HolName not in (Select HolidayName from Holidays where Year(HolidayDate)=[Year]); .... where [Year] is the year for which you are adding the records. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Below is the code for the three support functions: NthWeekdayInMonth ClosestWeekdayTo EasterDate (I take no credit for EasterDate!) =============== start code ======================= Public Function NthWeekdayInMonth _ (y As Integer, m As Integer, _ wd As Integer, n As Integer) _ As Date ' Find the date of the nth WeekDay (wd) of the month (m) ' in given year (y) [d=1:Sunday, 2:Monday, etc] ' if n is negative, then the nth-to-last Dim dt As Date If n 0 Then dt = DateSerial(y, m, 1) dt = dt + (wd - Weekday(dt) + 7) Mod 7 dt = dt + (n - 1) * 7 Else dt = DateSerial(y, m + 1, 0) dt = dt - (Weekday(dt) - wd + 7) Mod 7 dt = dt + (n + 1) * 7 End If NthWeekdayInMonth = dt End Function Public Function ClosestWeekdayTo _ (ByVal dt As Date, wd As Integer, _ Optional ByVal direction As Integer) _ As Date ' Find the date of the WeekDay (wd) closest to ' the given date (e.g. closest Monday to 1 March 1996) ' If dt falls on the given wd, return dt ' If direction is -ve, return first wd BEFORE the date ' If direction is +ve, return first wd AFTER the date ' If direction=0 (default) return CLOSEST wd If Weekday(dt) wd Then If direction = 0 Then If (Weekday(dt) - wd + 10) Mod 7 3 Then direction = -1 Else direction = 1 End If End If If direction 0 Then dt = dt - (Weekday(dt) - wd + 7) Mod 7 Else dt = dt + (wd - Weekday(dt) + 7) Mod 7 End If End If ClosestWeekdayTo = dt End Function Public Function EasterDate(y As Integer) As Date ' ' CALCULATE EASTER SUNDAY DATE ' ' Adapted from an algorithm by Ronald W. Mallen ' http://www.assa.org.au/edm.html ' ' y is a 4 digit year 1583 to 4099 ' ' Easter Sunday is the Sunday following the ' Paschal Full Moon (PFM) date for the year ' This algorithm is an arithmetic interpretation of the 3 step ' Easter Dating Method developed by Ron Mallen 1985, as a vast ' improvement on the method described in the Common Prayer Book ' ' Because this algorithm is a direct translation of the ' official tables, it can be easily proved to be 100% correct ' ' This algorithm derives values by sequential inter-dependent ' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS! ' ' All variables are integer data types ' ' It's free! Please do not modify code or comments! Dim d As Integer, m As Integer Dim Century As Integer, Remain19 As Integer, _ temp As Integer 'intermediate results Dim tA As Integer, tB As Integer, tC As Integer, _ tD As Integer, tE As Integer 'table A to E results Century = y \ 100 'first 2 digits of year Remain19 = y Mod 19 'remainder of year / 19 'calculate PFM date temp = (Century - 15) \ 2 + 202 - 11 * Remain19 If Century 26 Then temp = temp - 1 If Century 38 Then temp = temp - 1 If ((Century = 21) Or (Century = 24) Or (Century = 25) _ Or (Century = 33) Or (Century = 36) Or (Century = 37)) _ Then temp = temp - 1 temp = temp Mod 30 tA = temp + 21 If temp = 29 Then tA = tA - 1 If (temp = 28 And Remain19 10) Then tA = tA - 1 'find the next Sunday tB = (tA - 19) Mod 7 tC = (40 - Century) Mod 4 If tC = 3 Then tC = tC + 1 If tC 1 Then tC = tC + 1 temp = y Mod 100 tD = (temp + temp \ 4) Mod 7 tE = ((20 - tB - tC - tD) Mod 7) + 1 d = tA + tE 'return the date If d 31 Then d = d - 31 m = 4 Else m = 3 End If EasterDate = DateSerial(y, m, d) End Function ================= end code ====================== "Billiam" wrote in message ... 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 |
#17
|
|||
|
|||
Holiday Table Design
PS: ...and it didn't cost you a cent!
Mind you, I have posted this code elsewhere, so knowing Steve it will be at least part of the "solution" that he sells you "for a modest fee" g -- Graham "Graham Mandeno" wrote in message ... Hi Billiam FWIW, this is what *I* do... Holidays are either: 1. On a set day (e.g. Christmas Day) 2. On the closest given day of the week to the set date (e.g. closest Monday to 25 Feb) 3. On a day relative to Easter (e.g. Good Friday, Easter Monday) 4. On the nth (or nth-to-last) day-of-week of the month (e.g. second Monday in June, last Thursday in November) Here is a function that I use to calculate holiday dates given those requirements: =============== start code ======================= Public Function CalcHoliday(y As Integer, _ m As Integer, d As Integer, nType As Integer, _ Optional closest As Integer) As Date Select Case nType Case 0 ' holiday is on the actual day of the year ' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 ) CalcHoliday = DateSerial(y, m, d) Case 6, 7, 8 ' holiday is on the weekday (c) closest to (before ' or after) the actual day of the year ' n=6:closest before, 7:closest after, 8:either way ' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 ) CalcHoliday = ClosestWeekdayTo( _ DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0)) Case 9 ' holiday is d days before/after Easter Sunday (m is ignored) ' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 ) CalcHoliday = EasterDate(y) + d Case Else ' holiday is on the nth WeekDay (d) of the month (m) ' (1=Sunday, 2=Monday, etc) ' if n is negative, then the nth-to-last ' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 ) CalcHoliday = NthWeekdayInMonth(y, m, d, nType) End Select End Function ================= end code ====================== It will calculate the date of a holiday in any year, using a method that depends upon the value of the third argument (nType) If nType is: 0 : holiday is on the given date (y/m/d) 6, 7, 8 : holiday is on the closest weekday (closest) to the given date (6 :closest before, 7:closest after, 8:closest either way 9 : holiday is d days relative to Easter +(1..4) : holiday is nth weekday (d) in month -(1..4) : holiday is nth-to-last weekday (d) in month It uses three other functions (see further down below). OK, now as well as having a Holidays table, as you describe, I have a table named PerennialHolidays with these fields: HolName: text, PK (e.g. "Christmas Day") HolMonth: integer HolDay: integer HolType: a number indicating the type of calculation (see nType above) HolClosestDay: optional integer HolOrder: numeric (just to keep them in order for maintenance) Records might look like this: Christmas Day, 12, 25, 0 Easter Monday, 0, 1, 9 (one day after Easter Sunday) Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov) Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May) etc. Now, to add all the holidays for a given year, one needs only run an append query, like this: Insert into Holidays (HolidayName, HolidayDate) select HolName, CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) ) from PerennialHolidays where HolName not in (Select HolidayName from Holidays where Year(HolidayDate)=[Year]); ... where [Year] is the year for which you are adding the records. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Below is the code for the three support functions: NthWeekdayInMonth ClosestWeekdayTo EasterDate (I take no credit for EasterDate!) =============== start code ======================= Public Function NthWeekdayInMonth _ (y As Integer, m As Integer, _ wd As Integer, n As Integer) _ As Date ' Find the date of the nth WeekDay (wd) of the month (m) ' in given year (y) [d=1:Sunday, 2:Monday, etc] ' if n is negative, then the nth-to-last Dim dt As Date If n 0 Then dt = DateSerial(y, m, 1) dt = dt + (wd - Weekday(dt) + 7) Mod 7 dt = dt + (n - 1) * 7 Else dt = DateSerial(y, m + 1, 0) dt = dt - (Weekday(dt) - wd + 7) Mod 7 dt = dt + (n + 1) * 7 End If NthWeekdayInMonth = dt End Function Public Function ClosestWeekdayTo _ (ByVal dt As Date, wd As Integer, _ Optional ByVal direction As Integer) _ As Date ' Find the date of the WeekDay (wd) closest to ' the given date (e.g. closest Monday to 1 March 1996) ' If dt falls on the given wd, return dt ' If direction is -ve, return first wd BEFORE the date ' If direction is +ve, return first wd AFTER the date ' If direction=0 (default) return CLOSEST wd If Weekday(dt) wd Then If direction = 0 Then If (Weekday(dt) - wd + 10) Mod 7 3 Then direction = -1 Else direction = 1 End If End If If direction 0 Then dt = dt - (Weekday(dt) - wd + 7) Mod 7 Else dt = dt + (wd - Weekday(dt) + 7) Mod 7 End If End If ClosestWeekdayTo = dt End Function Public Function EasterDate(y As Integer) As Date ' ' CALCULATE EASTER SUNDAY DATE ' ' Adapted from an algorithm by Ronald W. Mallen ' http://www.assa.org.au/edm.html ' ' y is a 4 digit year 1583 to 4099 ' ' Easter Sunday is the Sunday following the ' Paschal Full Moon (PFM) date for the year ' This algorithm is an arithmetic interpretation of the 3 step ' Easter Dating Method developed by Ron Mallen 1985, as a vast ' improvement on the method described in the Common Prayer Book ' ' Because this algorithm is a direct translation of the ' official tables, it can be easily proved to be 100% correct ' ' This algorithm derives values by sequential inter-dependent ' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS! ' ' All variables are integer data types ' ' It's free! Please do not modify code or comments! Dim d As Integer, m As Integer Dim Century As Integer, Remain19 As Integer, _ temp As Integer 'intermediate results Dim tA As Integer, tB As Integer, tC As Integer, _ tD As Integer, tE As Integer 'table A to E results Century = y \ 100 'first 2 digits of year Remain19 = y Mod 19 'remainder of year / 19 'calculate PFM date temp = (Century - 15) \ 2 + 202 - 11 * Remain19 If Century 26 Then temp = temp - 1 If Century 38 Then temp = temp - 1 If ((Century = 21) Or (Century = 24) Or (Century = 25) _ Or (Century = 33) Or (Century = 36) Or (Century = 37)) _ Then temp = temp - 1 temp = temp Mod 30 tA = temp + 21 If temp = 29 Then tA = tA - 1 If (temp = 28 And Remain19 10) Then tA = tA - 1 'find the next Sunday tB = (tA - 19) Mod 7 tC = (40 - Century) Mod 4 If tC = 3 Then tC = tC + 1 If tC 1 Then tC = tC + 1 temp = y Mod 100 tD = (temp + temp \ 4) Mod 7 tE = ((20 - tB - tC - tD) Mod 7) + 1 d = tA + tE 'return the date If d 31 Then d = d - 31 m = 4 Else m = 3 End If EasterDate = DateSerial(y, m, d) End Function ================= end code ====================== "Billiam" wrote in message ... 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 |
#18
|
|||
|
|||
Holiday Table Design
Thank you Graham, and with great advice like yours, that is priceless!
Thank you very very VERY much! Billiam "Graham Mandeno" wrote: PS: ...and it didn't cost you a cent! Mind you, I have posted this code elsewhere, so knowing Steve it will be at least part of the "solution" that he sells you "for a modest fee" g -- Graham "Graham Mandeno" wrote in message ... Hi Billiam FWIW, this is what *I* do... Holidays are either: 1. On a set day (e.g. Christmas Day) 2. On the closest given day of the week to the set date (e.g. closest Monday to 25 Feb) 3. On a day relative to Easter (e.g. Good Friday, Easter Monday) 4. On the nth (or nth-to-last) day-of-week of the month (e.g. second Monday in June, last Thursday in November) Here is a function that I use to calculate holiday dates given those requirements: =============== start code ======================= Public Function CalcHoliday(y As Integer, _ m As Integer, d As Integer, nType As Integer, _ Optional closest As Integer) As Date Select Case nType Case 0 ' holiday is on the actual day of the year ' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 ) CalcHoliday = DateSerial(y, m, d) Case 6, 7, 8 ' holiday is on the weekday (c) closest to (before ' or after) the actual day of the year ' n=6:closest before, 7:closest after, 8:either way ' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 ) CalcHoliday = ClosestWeekdayTo( _ DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0)) Case 9 ' holiday is d days before/after Easter Sunday (m is ignored) ' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 ) CalcHoliday = EasterDate(y) + d Case Else ' holiday is on the nth WeekDay (d) of the month (m) ' (1=Sunday, 2=Monday, etc) ' if n is negative, then the nth-to-last ' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 ) CalcHoliday = NthWeekdayInMonth(y, m, d, nType) End Select End Function ================= end code ====================== It will calculate the date of a holiday in any year, using a method that depends upon the value of the third argument (nType) If nType is: 0 : holiday is on the given date (y/m/d) 6, 7, 8 : holiday is on the closest weekday (closest) to the given date (6 :closest before, 7:closest after, 8:closest either way 9 : holiday is d days relative to Easter +(1..4) : holiday is nth weekday (d) in month -(1..4) : holiday is nth-to-last weekday (d) in month It uses three other functions (see further down below). OK, now as well as having a Holidays table, as you describe, I have a table named PerennialHolidays with these fields: HolName: text, PK (e.g. "Christmas Day") HolMonth: integer HolDay: integer HolType: a number indicating the type of calculation (see nType above) HolClosestDay: optional integer HolOrder: numeric (just to keep them in order for maintenance) Records might look like this: Christmas Day, 12, 25, 0 Easter Monday, 0, 1, 9 (one day after Easter Sunday) Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov) Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May) etc. Now, to add all the holidays for a given year, one needs only run an append query, like this: Insert into Holidays (HolidayName, HolidayDate) select HolName, CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) ) from PerennialHolidays where HolName not in (Select HolidayName from Holidays where Year(HolidayDate)=[Year]); ... where [Year] is the year for which you are adding the records. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Below is the code for the three support functions: NthWeekdayInMonth ClosestWeekdayTo EasterDate (I take no credit for EasterDate!) =============== start code ======================= Public Function NthWeekdayInMonth _ (y As Integer, m As Integer, _ wd As Integer, n As Integer) _ As Date ' Find the date of the nth WeekDay (wd) of the month (m) ' in given year (y) [d=1:Sunday, 2:Monday, etc] ' if n is negative, then the nth-to-last Dim dt As Date If n 0 Then dt = DateSerial(y, m, 1) dt = dt + (wd - Weekday(dt) + 7) Mod 7 dt = dt + (n - 1) * 7 Else dt = DateSerial(y, m + 1, 0) dt = dt - (Weekday(dt) - wd + 7) Mod 7 dt = dt + (n + 1) * 7 End If NthWeekdayInMonth = dt End Function Public Function ClosestWeekdayTo _ (ByVal dt As Date, wd As Integer, _ Optional ByVal direction As Integer) _ As Date ' Find the date of the WeekDay (wd) closest to ' the given date (e.g. closest Monday to 1 March 1996) ' If dt falls on the given wd, return dt ' If direction is -ve, return first wd BEFORE the date ' If direction is +ve, return first wd AFTER the date ' If direction=0 (default) return CLOSEST wd If Weekday(dt) wd Then If direction = 0 Then If (Weekday(dt) - wd + 10) Mod 7 3 Then direction = -1 Else direction = 1 End If End If If direction 0 Then dt = dt - (Weekday(dt) - wd + 7) Mod 7 Else dt = dt + (wd - Weekday(dt) + 7) Mod 7 End If End If ClosestWeekdayTo = dt End Function Public Function EasterDate(y As Integer) As Date ' ' CALCULATE EASTER SUNDAY DATE ' ' Adapted from an algorithm by Ronald W. Mallen ' http://www.assa.org.au/edm.html ' ' y is a 4 digit year 1583 to 4099 ' ' Easter Sunday is the Sunday following the ' Paschal Full Moon (PFM) date for the year ' This algorithm is an arithmetic interpretation of the 3 step ' Easter Dating Method developed by Ron Mallen 1985, as a vast ' improvement on the method described in the Common Prayer Book ' ' Because this algorithm is a direct translation of the ' official tables, it can be easily proved to be 100% correct ' ' This algorithm derives values by sequential inter-dependent ' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS! ' ' All variables are integer data types ' ' It's free! Please do not modify code or comments! Dim d As Integer, m As Integer Dim Century As Integer, Remain19 As Integer, _ temp As Integer 'intermediate results Dim tA As Integer, tB As Integer, tC As Integer, _ tD As Integer, tE As Integer 'table A to E results Century = y \ 100 'first 2 digits of year Remain19 = y Mod 19 'remainder of year / 19 'calculate PFM date temp = (Century - 15) \ 2 + 202 - 11 * Remain19 If Century 26 Then temp = temp - 1 If Century 38 Then temp = temp - 1 If ((Century = 21) Or (Century = 24) Or (Century = 25) _ Or (Century = 33) Or (Century = 36) Or (Century = 37)) _ Then temp = temp - 1 temp = temp Mod 30 tA = temp + 21 If temp = 29 Then tA = tA - 1 If (temp = 28 And Remain19 10) Then tA = tA - 1 'find the next Sunday tB = (tA - 19) Mod 7 tC = (40 - Century) Mod 4 If tC = 3 Then tC = tC + 1 If tC 1 Then tC = tC + 1 temp = y Mod 100 tD = (temp + temp \ 4) Mod 7 tE = ((20 - tB - tC - tD) Mod 7) + 1 d = tA + tE 'return the date If d 31 Then d = d - 31 m = 4 Else m = 3 End If EasterDate = DateSerial(y, m, d) End Function ================= end code ====================== "Billiam" wrote in message ... 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 |
#19
|
|||
|
|||
Holiday Table Design
I will give this a try, Graham! Thanks for the detailed post! If I understand
you correctly, this would actually calculate Easter say the year 2019? Billiam "Graham Mandeno" wrote: Hi Billiam FWIW, this is what *I* do... Holidays are either: 1. On a set day (e.g. Christmas Day) 2. On the closest given day of the week to the set date (e.g. closest Monday to 25 Feb) 3. On a day relative to Easter (e.g. Good Friday, Easter Monday) 4. On the nth (or nth-to-last) day-of-week of the month (e.g. second Monday in June, last Thursday in November) Here is a function that I use to calculate holiday dates given those requirements: =============== start code ======================= Public Function CalcHoliday(y As Integer, _ m As Integer, d As Integer, nType As Integer, _ Optional closest As Integer) As Date Select Case nType Case 0 ' holiday is on the actual day of the year ' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 ) CalcHoliday = DateSerial(y, m, d) Case 6, 7, 8 ' holiday is on the weekday (c) closest to (before ' or after) the actual day of the year ' n=6:closest before, 7:closest after, 8:either way ' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 ) CalcHoliday = ClosestWeekdayTo( _ DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0)) Case 9 ' holiday is d days before/after Easter Sunday (m is ignored) ' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 ) CalcHoliday = EasterDate(y) + d Case Else ' holiday is on the nth WeekDay (d) of the month (m) ' (1=Sunday, 2=Monday, etc) ' if n is negative, then the nth-to-last ' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 ) CalcHoliday = NthWeekdayInMonth(y, m, d, nType) End Select End Function ================= end code ====================== It will calculate the date of a holiday in any year, using a method that depends upon the value of the third argument (nType) If nType is: 0 : holiday is on the given date (y/m/d) 6, 7, 8 : holiday is on the closest weekday (closest) to the given date (6 :closest before, 7:closest after, 8:closest either way 9 : holiday is d days relative to Easter +(1..4) : holiday is nth weekday (d) in month -(1..4) : holiday is nth-to-last weekday (d) in month It uses three other functions (see further down below). OK, now as well as having a Holidays table, as you describe, I have a table named PerennialHolidays with these fields: HolName: text, PK (e.g. "Christmas Day") HolMonth: integer HolDay: integer HolType: a number indicating the type of calculation (see nType above) HolClosestDay: optional integer HolOrder: numeric (just to keep them in order for maintenance) Records might look like this: Christmas Day, 12, 25, 0 Easter Monday, 0, 1, 9 (one day after Easter Sunday) Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov) Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May) etc. Now, to add all the holidays for a given year, one needs only run an append query, like this: Insert into Holidays (HolidayName, HolidayDate) select HolName, CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) ) from PerennialHolidays where HolName not in (Select HolidayName from Holidays where Year(HolidayDate)=[Year]); .... where [Year] is the year for which you are adding the records. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Below is the code for the three support functions: NthWeekdayInMonth ClosestWeekdayTo EasterDate (I take no credit for EasterDate!) =============== start code ======================= Public Function NthWeekdayInMonth _ (y As Integer, m As Integer, _ wd As Integer, n As Integer) _ As Date ' Find the date of the nth WeekDay (wd) of the month (m) ' in given year (y) [d=1:Sunday, 2:Monday, etc] ' if n is negative, then the nth-to-last Dim dt As Date If n 0 Then dt = DateSerial(y, m, 1) dt = dt + (wd - Weekday(dt) + 7) Mod 7 dt = dt + (n - 1) * 7 Else dt = DateSerial(y, m + 1, 0) dt = dt - (Weekday(dt) - wd + 7) Mod 7 dt = dt + (n + 1) * 7 End If NthWeekdayInMonth = dt End Function Public Function ClosestWeekdayTo _ (ByVal dt As Date, wd As Integer, _ Optional ByVal direction As Integer) _ As Date ' Find the date of the WeekDay (wd) closest to ' the given date (e.g. closest Monday to 1 March 1996) ' If dt falls on the given wd, return dt ' If direction is -ve, return first wd BEFORE the date ' If direction is +ve, return first wd AFTER the date ' If direction=0 (default) return CLOSEST wd If Weekday(dt) wd Then If direction = 0 Then If (Weekday(dt) - wd + 10) Mod 7 3 Then direction = -1 Else direction = 1 End If End If If direction 0 Then dt = dt - (Weekday(dt) - wd + 7) Mod 7 Else dt = dt + (wd - Weekday(dt) + 7) Mod 7 End If End If ClosestWeekdayTo = dt End Function Public Function EasterDate(y As Integer) As Date ' ' CALCULATE EASTER SUNDAY DATE ' ' Adapted from an algorithm by Ronald W. Mallen ' http://www.assa.org.au/edm.html ' ' y is a 4 digit year 1583 to 4099 ' ' Easter Sunday is the Sunday following the ' Paschal Full Moon (PFM) date for the year ' This algorithm is an arithmetic interpretation of the 3 step ' Easter Dating Method developed by Ron Mallen 1985, as a vast ' improvement on the method described in the Common Prayer Book ' ' Because this algorithm is a direct translation of the ' official tables, it can be easily proved to be 100% correct ' ' This algorithm derives values by sequential inter-dependent ' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS! ' ' All variables are integer data types ' ' It's free! Please do not modify code or comments! Dim d As Integer, m As Integer Dim Century As Integer, Remain19 As Integer, _ temp As Integer 'intermediate results Dim tA As Integer, tB As Integer, tC As Integer, _ tD As Integer, tE As Integer 'table A to E results Century = y \ 100 'first 2 digits of year Remain19 = y Mod 19 'remainder of year / 19 'calculate PFM date temp = (Century - 15) \ 2 + 202 - 11 * Remain19 If Century 26 Then temp = temp - 1 If Century 38 Then temp = temp - 1 If ((Century = 21) Or (Century = 24) Or (Century = 25) _ Or (Century = 33) Or (Century = 36) Or (Century = 37)) _ Then temp = temp - 1 temp = temp Mod 30 tA = temp + 21 If temp = 29 Then tA = tA - 1 If (temp = 28 And Remain19 10) Then tA = tA - 1 'find the next Sunday tB = (tA - 19) Mod 7 tC = (40 - Century) Mod 4 If tC = 3 Then tC = tC + 1 If tC 1 Then tC = tC + 1 temp = y Mod 100 tD = (temp + temp \ 4) Mod 7 tE = ((20 - tB - tC - tD) Mod 7) + 1 d = tA + tE 'return the date If d 31 Then d = d - 31 m = 4 Else m = 3 End If EasterDate = DateSerial(y, m, d) End Function ================= end code ====================== "Billiam" wrote in message ... 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 |
#20
|
|||
|
|||
Holiday Table Design
Hi Billiam
You're welcome! this would actually calculate Easter say the year 2019? Sure would! Any year from 1583 to 4099, so your database will long out-live you ;-) -- Graham "Billiam" wrote in message ... I will give this a try, Graham! Thanks for the detailed post! If I understand you correctly, this would actually calculate Easter say the year 2019? Billiam "Graham Mandeno" wrote: Hi Billiam FWIW, this is what *I* do... Holidays are either: 1. On a set day (e.g. Christmas Day) 2. On the closest given day of the week to the set date (e.g. closest Monday to 25 Feb) 3. On a day relative to Easter (e.g. Good Friday, Easter Monday) 4. On the nth (or nth-to-last) day-of-week of the month (e.g. second Monday in June, last Thursday in November) Here is a function that I use to calculate holiday dates given those requirements: =============== start code ======================= Public Function CalcHoliday(y As Integer, _ m As Integer, d As Integer, nType As Integer, _ Optional closest As Integer) As Date Select Case nType Case 0 ' holiday is on the actual day of the year ' e.g. Christmas Day: CalcHoliday( y, 12, 25, 0 ) CalcHoliday = DateSerial(y, m, d) Case 6, 7, 8 ' holiday is on the weekday (c) closest to (before ' or after) the actual day of the year ' n=6:closest before, 7:closest after, 8:either way ' e.g. closest Monday to 4 July : CalcHoliday( y, 7, 4, 8, 2 ) CalcHoliday = ClosestWeekdayTo( _ DateSerial(y, m, d), closest, Choose(nType - 5, -1, 1, 0)) Case 9 ' holiday is d days before/after Easter Sunday (m is ignored) ' e.g. Good Friday: CalcHoliday( y, 0, -2, 9 ) CalcHoliday = EasterDate(y) + d Case Else ' holiday is on the nth WeekDay (d) of the month (m) ' (1=Sunday, 2=Monday, etc) ' if n is negative, then the nth-to-last ' e.g. Last Monday of October: CalcHoliday( y, 10, 2, -1 ) CalcHoliday = NthWeekdayInMonth(y, m, d, nType) End Select End Function ================= end code ====================== It will calculate the date of a holiday in any year, using a method that depends upon the value of the third argument (nType) If nType is: 0 : holiday is on the given date (y/m/d) 6, 7, 8 : holiday is on the closest weekday (closest) to the given date (6 :closest before, 7:closest after, 8:closest either way 9 : holiday is d days relative to Easter +(1..4) : holiday is nth weekday (d) in month -(1..4) : holiday is nth-to-last weekday (d) in month It uses three other functions (see further down below). OK, now as well as having a Holidays table, as you describe, I have a table named PerennialHolidays with these fields: HolName: text, PK (e.g. "Christmas Day") HolMonth: integer HolDay: integer HolType: a number indicating the type of calculation (see nType above) HolClosestDay: optional integer HolOrder: numeric (just to keep them in order for maintenance) Records might look like this: Christmas Day, 12, 25, 0 Easter Monday, 0, 1, 9 (one day after Easter Sunday) Thanksgiving Day, 11, 5, 4 (4th Thurs in Nov) Groundhog Day: 5, 2, 8, 3 (closest Tuesday to 2 May) etc. Now, to add all the holidays for a given year, one needs only run an append query, like this: Insert into Holidays (HolidayName, HolidayDate) select HolName, CalcHoliday( [Year], HolMonth, HolDay, HolType, Nz(HolClosestDay, 0) ) from PerennialHolidays where HolName not in (Select HolidayName from Holidays where Year(HolidayDate)=[Year]); .... where [Year] is the year for which you are adding the records. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand Below is the code for the three support functions: NthWeekdayInMonth ClosestWeekdayTo EasterDate (I take no credit for EasterDate!) =============== start code ======================= Public Function NthWeekdayInMonth _ (y As Integer, m As Integer, _ wd As Integer, n As Integer) _ As Date ' Find the date of the nth WeekDay (wd) of the month (m) ' in given year (y) [d=1:Sunday, 2:Monday, etc] ' if n is negative, then the nth-to-last Dim dt As Date If n 0 Then dt = DateSerial(y, m, 1) dt = dt + (wd - Weekday(dt) + 7) Mod 7 dt = dt + (n - 1) * 7 Else dt = DateSerial(y, m + 1, 0) dt = dt - (Weekday(dt) - wd + 7) Mod 7 dt = dt + (n + 1) * 7 End If NthWeekdayInMonth = dt End Function Public Function ClosestWeekdayTo _ (ByVal dt As Date, wd As Integer, _ Optional ByVal direction As Integer) _ As Date ' Find the date of the WeekDay (wd) closest to ' the given date (e.g. closest Monday to 1 March 1996) ' If dt falls on the given wd, return dt ' If direction is -ve, return first wd BEFORE the date ' If direction is +ve, return first wd AFTER the date ' If direction=0 (default) return CLOSEST wd If Weekday(dt) wd Then If direction = 0 Then If (Weekday(dt) - wd + 10) Mod 7 3 Then direction = -1 Else direction = 1 End If End If If direction 0 Then dt = dt - (Weekday(dt) - wd + 7) Mod 7 Else dt = dt + (wd - Weekday(dt) + 7) Mod 7 End If End If ClosestWeekdayTo = dt End Function Public Function EasterDate(y As Integer) As Date ' ' CALCULATE EASTER SUNDAY DATE ' ' Adapted from an algorithm by Ronald W. Mallen ' http://www.assa.org.au/edm.html ' ' y is a 4 digit year 1583 to 4099 ' ' Easter Sunday is the Sunday following the ' Paschal Full Moon (PFM) date for the year ' This algorithm is an arithmetic interpretation of the 3 step ' Easter Dating Method developed by Ron Mallen 1985, as a vast ' improvement on the method described in the Common Prayer Book ' ' Because this algorithm is a direct translation of the ' official tables, it can be easily proved to be 100% correct ' ' This algorithm derives values by sequential inter-dependent ' calculations, so ... DO NOT MODIFY THE ORDER OF CALCULATIONS! ' ' All variables are integer data types ' ' It's free! Please do not modify code or comments! Dim d As Integer, m As Integer Dim Century As Integer, Remain19 As Integer, _ temp As Integer 'intermediate results Dim tA As Integer, tB As Integer, tC As Integer, _ tD As Integer, tE As Integer 'table A to E results Century = y \ 100 'first 2 digits of year Remain19 = y Mod 19 'remainder of year / 19 'calculate PFM date temp = (Century - 15) \ 2 + 202 - 11 * Remain19 If Century 26 Then temp = temp - 1 If Century 38 Then temp = temp - 1 If ((Century = 21) Or (Century = 24) Or (Century = 25) _ Or (Century = 33) Or (Century = 36) Or (Century = 37)) _ Then temp = temp - 1 temp = temp Mod 30 tA = temp + 21 If temp = 29 Then tA = tA - 1 If (temp = 28 And Remain19 10) Then tA = tA - 1 'find the next Sunday tB = (tA - 19) Mod 7 tC = (40 - Century) Mod 4 If tC = 3 Then tC = tC + 1 If tC 1 Then tC = tC + 1 temp = y Mod 100 tD = (temp + temp \ 4) Mod 7 tE = ((20 - tB - tC - tD) Mod 7) + 1 d = tA + tE 'return the date If d 31 Then d = d - 31 m = 4 Else m = 3 End If EasterDate = DateSerial(y, m, d) End Function ================= end code ====================== "Billiam" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|