|
| 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 |
|
#1
October 3rd, 2009, 08:57 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Holiday Table Design
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 
|
|
#2
October 4th, 2009, 01:37 AM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Holiday Table Design
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]
|
|
#3
October 4th, 2009, 06:17 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Holiday Table Design
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]
|
|
#4
October 4th, 2009, 06:25 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Holiday Table Design
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]
|
|
#5
October 4th, 2009, 06:40 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Holiday Table Design
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]
|
|
#6
October 4th, 2009, 06:49 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Holiday Table Design
Actually, for me Access is a good addiction. It has paid my mortgage and
bought me some nice guitars and guns over the last ten years  No, I don't know of any such routine. Why go to the extra trouble to put it in Excel? Why not just enter it directly in your holiday table? But, never, ever allow users to work directly in tables. -- Dave Hargis, Microsoft Access MVP "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? 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]
|
|
#7
October 4th, 2009, 06:56 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Holiday Table Design
Yes, if you are able to grsp Access it is definately a great addiction...for
those of us that struggle with it, it can REALLY BE A NIGHTMARE!!$#@%! As I said though, if you stick with it, the results are worth it when you design something from the ground up and it works like a dream...that's what keeps me hooked. The reason I suggested Excel was I would simply enter the day once and then autofill the whole lot for a complete decade table. Then I would simply add the days which are based on calcuation each year, which is only a small percentage of the lot in my case. I suspect some expression could be made, perhaps some iterated looping function of year plus one for 10 loops, but I am new to this stuff, and do not have the time to play...so just thought Excel would be easier at my skill level. If there is an easy way to do this in Access 2007, or has been posted on, I happy to learn how if anyone is willing? Thanks again, Billiam "Klatuu" wrote: Actually, for me Access is a good addiction. It has paid my mortgage and bought me some nice guitars and guns over the last ten years No, I don't know of any such routine. Why go to the extra trouble to put it in Excel? Why not just enter it directly in your holiday table? But, never, ever allow users to work directly in tables. -- Dave Hargis, Microsoft Access MVP "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? 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]
|
|
#8
October 4th, 2009, 07:16 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Holiday Table Design
Excel is okay, sounds like it may be a good shortcut for you.
-- Dave Hargis, Microsoft Access MVP "Billiam" wrote: Yes, if you are able to grsp Access it is definately a great addiction...for those of us that struggle with it, it can REALLY BE A NIGHTMARE!!$#@%! As I said though, if you stick with it, the results are worth it when you design something from the ground up and it works like a dream...that's what keeps me hooked. The reason I suggested Excel was I would simply enter the day once and then autofill the whole lot for a complete decade table. Then I would simply add the days which are based on calcuation each year, which is only a small percentage of the lot in my case. I suspect some expression could be made, perhaps some iterated looping function of year plus one for 10 loops, but I am new to this stuff, and do not have the time to play...so just thought Excel would be easier at my skill level. If there is an easy way to do this in Access 2007, or has been posted on, I happy to learn how if anyone is willing? Thanks again, Billiam "Klatuu" wrote: Actually, for me Access is a good addiction. It has paid my mortgage and bought me some nice guitars and guns over the last ten years No, I don't know of any such routine. Why go to the extra trouble to put it in Excel? Why not just enter it directly in your holiday table? But, never, ever allow users to work directly in tables. -- Dave Hargis, Microsoft Access MVP "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? 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]
|
|
#9
October 4th, 2009, 10:05 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Holiday Table Design
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 | |
|
|
Linear Mode
