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]