Calendar reports

[Stephanie]

Hi there! I've been looking at Duane H's calendar db. I still don't know
what I'm doing!

I've created a query that brings back the fields I need. Here's the
concept: There are on-going volunteering opportunities (VolunteerName)
associated with organizations (OrganizationName). Each event has a beginning
and ending time (EventStart, EventEnd), a frequency (1xweek, 2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days of the week
with yes/no boxes). Each on-going volunteering opportunity has an associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month that shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and Coordinator to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in case it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday, Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday, Nz([NickName],[FirstName]) & " "
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID) INNER JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND ((Event.Coordinator)=Yes));

 

[Duane Hookom]

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are storing in
your tables without seeing sample records.

 

[Stephanie]

Duane,
I was having difficulty with Days and Frequency, so someone in the Design
group directed me toward 7 check boxes. If I have a frequency of 2xWeek, I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me to choose
one of the days. I thought the check box method didn't seem "regulation" but
it's working so far, but it sounds as though I should change it to help with
the calendar design (and I need all the "normal" I can get!). I'd appreciate
any suggestions. I originally had Frequency FK in Volunteering table and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes) to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the calendar
report, I'd show this "event" every week and occurring on Wed. and Fri.

Hope that explains a bit better about where I'm trying to go. Thanks. I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are storing in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

Hi there! I've been looking at Duane H's calendar db. I still don't know
what I'm doing!

I've created a query that brings back the fields I need. Here's the
concept: There are on-going volunteering opportunities (VolunteerName)
associated with organizations (OrganizationName). Each event has a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek, 2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days of the
week
with yes/no boxes). Each on-going volunteering opportunity has an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month that shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in case it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday, Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday, Nz([NickName],[FirstName]) & "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID) INNER JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[PC Datasheet]

I have a calendar scheduling module that will do just what you want. You can
select Shriner's Hospital from a drop down list in the menu at the top of
the screen and then enter Reading with kids, EventStart 11:00PM and
EventEnd 12:00 PM and the frequency of twice a week directly into a
calendar form. The calendar of events for any given year amd month can be
printed out in a calendar report. Calendar form and calendar report mean
just as the name implies; the form and report look like a page off a
calendar. If you are interested, send me an email and I will send you a
screen shot.

 

[Duane Hookom]

I don't agree with your structure or your method for storing the "x". Values
like 3xWeek and 2xMonth have no utility value. You would be better of with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week would be 12
times per month.

It would really be help if you would take the time to type a few records
(significant fields only) and how you would expect to see these in a report.

--
Duane Hookom
MS Access MVP

Duane,
I was having difficulty with Days and Frequency, so someone in the Design
group directed me toward 7 check boxes. If I have a frequency of 2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me to choose
one of the days. I thought the check box method didn't seem "regulation"
but
it's working so far, but it sounds as though I should change it to help
with
the calendar design (and I need all the "normal" I can get!). I'd
appreciate
any suggestions. I originally had Frequency FK in Volunteering table and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes) to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the calendar
report, I'd show this "event" every week and occurring on Wed. and Fri.

Hope that explains a bit better about where I'm trying to go. Thanks.
I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are storing in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

Hi there! I've been looking at Duane H's calendar db. I still don't
know
what I'm doing!

I've created a query that brings back the fields I need. Here's the
concept: There are on-going volunteering opportunities (VolunteerName)
associated with organizations (OrganizationName). Each event has a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek, 2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days of
the
week
with yes/no boxes). Each on-going volunteering opportunity has an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month that
shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and
Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in case it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday, Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday, Nz([NickName],[FirstName])
& "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID) INNER
JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[Stephanie]

Thanks for the tips. I like the idea of NumPer and DateIncr, and I added them
to my Volunteering table (getting rid of my previous frequency and day
fields).

However, I do need to know what days of the week are involoved and which
week of the month. Our opportunities read as: 1st Monday of the month; 2nd
and 4th Wednesday of the month; the 5th Tuesday of the month. I would
appreciate suggestions on how to handle these requirements in my tables.

I'm hoping for a report that I will give to our volunteers showing all of
the volunteer opportunities avaialable to them for a generic 5-week month
(essentially 35 day boxes: Sunday to Saturday across the top, 5 rows of
weeks). If a volunteer opportunity is on the 2nd and 4th Wednesday of every
month, I'd like the report to have the volunteer information on the 2nd
Wednesday (11th box position) and the 4th Wednesday (25th box position).

I apologize, I thought I had explained what fields I'd like to see on the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks involoved and
which week of the month
(that will help the opportunities are tied together- the volunteer won't
just think they'll volunteer on the 2nd Wednesday without realizing that the
4th Wednesday is part of the same volunteering opportunity; or that they
volunteer on Tuesday one week when the opportunity is Tuesday and Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can help.

I don't agree with your structure or your method for storing the "x". Values
like 3xWeek and 2xMonth have no utility value. You would be better of with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week would be 12
times per month.

It would really be help if you would take the time to type a few records
(significant fields only) and how you would expect to see these in a report.

--
Duane Hookom
MS Access MVP

Duane,
I was having difficulty with Days and Frequency, so someone in the Design
group directed me toward 7 check boxes. If I have a frequency of 2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me to choose
one of the days. I thought the check box method didn't seem "regulation"
but
it's working so far, but it sounds as though I should change it to help
with
the calendar design (and I need all the "normal" I can get!). I'd
appreciate
any suggestions. I originally had Frequency FK in Volunteering table and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes) to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the calendar
report, I'd show this "event" every week and occurring on Wed. and Fri.

Hope that explains a bit better about where I'm trying to go. Thanks.
I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are storing in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

Hi there! I've been looking at Duane H's calendar db. I still don't
know
what I'm doing!

I've created a query that brings back the fields I need. Here's the
concept: There are on-going volunteering opportunities (VolunteerName)
associated with organizations (OrganizationName). Each event has a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek, 2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days of
the
week
with yes/no boxes). Each on-going volunteering opportunity has an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month that
shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and
Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in case it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday, Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday, Nz([NickName],[FirstName])
& "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID) INNER
JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[Duane Hookom]

I would create a related table with one record per each opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3 and be for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP

Thanks for the tips. I like the idea of NumPer and DateIncr, and I added
them
to my Volunteering table (getting rid of my previous frequency and day
fields).

However, I do need to know what days of the week are involoved and which
week of the month. Our opportunities read as: 1st Monday of the month; 2nd
and 4th Wednesday of the month; the 5th Tuesday of the month. I would
appreciate suggestions on how to handle these requirements in my tables.

I'm hoping for a report that I will give to our volunteers showing all of
the volunteer opportunities avaialable to them for a generic 5-week month
(essentially 35 day boxes: Sunday to Saturday across the top, 5 rows of
weeks). If a volunteer opportunity is on the 2nd and 4th Wednesday of
every
month, I'd like the report to have the volunteer information on the 2nd
Wednesday (11th box position) and the 4th Wednesday (25th box position).

I apologize, I thought I had explained what fields I'd like to see on the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks involoved
and
which week of the month
(that will help the opportunities are tied together- the volunteer won't
just think they'll volunteer on the 2nd Wednesday without realizing that
the
4th Wednesday is part of the same volunteering opportunity; or that they
volunteer on Tuesday one week when the opportunity is Tuesday and
Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can help.

I don't agree with your structure or your method for storing the "x".
Values
like 3xWeek and 2xMonth have no utility value. You would be better of
with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week would be 12
times per month.

It would really be help if you would take the time to type a few records
(significant fields only) and how you would expect to see these in a
report.

--
Duane Hookom
MS Access MVP

Duane,
I was having difficulty with Days and Frequency, so someone in the
Design
group directed me toward 7 check boxes. If I have a frequency of
2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me to
choose
one of the days. I thought the check box method didn't seem
"regulation"
but
it's working so far, but it sounds as though I should change it to help
with
the calendar design (and I need all the "normal" I can get!). I'd
appreciate
any suggestions. I originally had Frequency FK in Volunteering table
and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes) to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the
calendar
report, I'd show this "event" every week and occurring on Wed. and Fri.

Hope that explains a bit better about where I'm trying to go. Thanks.
I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID


:

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are storing
in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

Hi there! I've been looking at Duane H's calendar db. I still
don't
know
what I'm doing!

I've created a query that brings back the fields I need. Here's the
concept: There are on-going volunteering opportunities
(VolunteerName)
associated with organizations (OrganizationName). Each event has a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek, 2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days of
the
week
with yes/no boxes). Each on-going volunteering opportunity has an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month that
shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and
Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in case
it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday, Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday,
Nz([NickName],[FirstName])
& "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID) INNER
JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[Stephanie]

Sweet! Thanks- I think my new table FrequencyDays is just what I'm looking
for. I appreciate the table design tips and I've created a datasheet subform
to select Weeks and Days on my Volunteering form.
I've adjusted my query to reflect Week and DayID: so Date Box 11 is Week 2,
DayID 4 and Date Box 25 is Week 4, DayID 4.

Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Now after all of your design work, I'm back to my Calendar question and
would appreciate any suggestions to head me down the path of a report with 35
Day Boxes showing my volunteer opportunities. Thanks for your time and for
making me more normal.

I would create a related table with one record per each opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3 and be for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP

Thanks for the tips. I like the idea of NumPer and DateIncr, and I added
them
to my Volunteering table (getting rid of my previous frequency and day
fields).

However, I do need to know what days of the week are involoved and which
week of the month. Our opportunities read as: 1st Monday of the month; 2nd
and 4th Wednesday of the month; the 5th Tuesday of the month. I would
appreciate suggestions on how to handle these requirements in my tables.

I'm hoping for a report that I will give to our volunteers showing all of
the volunteer opportunities avaialable to them for a generic 5-week month
(essentially 35 day boxes: Sunday to Saturday across the top, 5 rows of
weeks). If a volunteer opportunity is on the 2nd and 4th Wednesday of
every
month, I'd like the report to have the volunteer information on the 2nd
Wednesday (11th box position) and the 4th Wednesday (25th box position).

I apologize, I thought I had explained what fields I'd like to see on the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks involoved
and
which week of the month
(that will help the opportunities are tied together- the volunteer won't
just think they'll volunteer on the 2nd Wednesday without realizing that
the
4th Wednesday is part of the same volunteering opportunity; or that they
volunteer on Tuesday one week when the opportunity is Tuesday and
Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can help.

I don't agree with your structure or your method for storing the "x".
Values
like 3xWeek and 2xMonth have no utility value. You would be better of
with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week would be 12
times per month.

It would really be help if you would take the time to type a few records
(significant fields only) and how you would expect to see these in a
report.

--
Duane Hookom
MS Access MVP


Duane,
I was having difficulty with Days and Frequency, so someone in the
Design
group directed me toward 7 check boxes. If I have a frequency of
2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me to
choose
one of the days. I thought the check box method didn't seem
"regulation"
but
it's working so far, but it sounds as though I should change it to help
with
the calendar design (and I need all the "normal" I can get!). I'd
appreciate
any suggestions. I originally had Frequency FK in Volunteering table
and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes) to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the
calendar
report, I'd show this "event" every week and occurring on Wed. and Fri.

Hope that explains a bit better about where I'm trying to go. Thanks.
I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID


:

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are storing
in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

Hi there! I've been looking at Duane H's calendar db. I still
don't
know
what I'm doing!

I've created a query that brings back the fields I need. Here's the
concept: There are on-going volunteering opportunities
(VolunteerName)
associated with organizations (OrganizationName). Each event has a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek, 2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days of
the
week
with yes/no boxes). Each on-going volunteering opportunity has an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month that
shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and
Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in case
it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday, Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday,
Nz([NickName],[FirstName])
& "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID) INNER
JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[Stephanie]

Okay, I think I have my subreport built:

OrganizationName (Shriner's)
ProgramName (Read with Kids)
Start and End (11:00 AM - 12:00 PM)

But I'm struggling with getting the information in the correct Date Box
(week, day). I'd appreciate any suggestions.

I would create a related table with one record per each opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3 and be for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP

Thanks for the tips. I like the idea of NumPer and DateIncr, and I added
them
to my Volunteering table (getting rid of my previous frequency and day
fields).

However, I do need to know what days of the week are involoved and which
week of the month. Our opportunities read as: 1st Monday of the month; 2nd
and 4th Wednesday of the month; the 5th Tuesday of the month. I would
appreciate suggestions on how to handle these requirements in my tables.

I'm hoping for a report that I will give to our volunteers showing all of
the volunteer opportunities avaialable to them for a generic 5-week month
(essentially 35 day boxes: Sunday to Saturday across the top, 5 rows of
weeks). If a volunteer opportunity is on the 2nd and 4th Wednesday of
every
month, I'd like the report to have the volunteer information on the 2nd
Wednesday (11th box position) and the 4th Wednesday (25th box position).

I apologize, I thought I had explained what fields I'd like to see on the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks involoved
and
which week of the month
(that will help the opportunities are tied together- the volunteer won't
just think they'll volunteer on the 2nd Wednesday without realizing that
the
4th Wednesday is part of the same volunteering opportunity; or that they
volunteer on Tuesday one week when the opportunity is Tuesday and
Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can help.

I don't agree with your structure or your method for storing the "x".
Values
like 3xWeek and 2xMonth have no utility value. You would be better of
with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week would be 12
times per month.

It would really be help if you would take the time to type a few records
(significant fields only) and how you would expect to see these in a
report.

--
Duane Hookom
MS Access MVP


Duane,
I was having difficulty with Days and Frequency, so someone in the
Design
group directed me toward 7 check boxes. If I have a frequency of
2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me to
choose
one of the days. I thought the check box method didn't seem
"regulation"
but
it's working so far, but it sounds as though I should change it to help
with
the calendar design (and I need all the "normal" I can get!). I'd
appreciate
any suggestions. I originally had Frequency FK in Volunteering table
and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes) to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the
calendar
report, I'd show this "event" every week and occurring on Wed. and Fri.

Hope that explains a bit better about where I'm trying to go. Thanks.
I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID


:

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are storing
in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

Hi there! I've been looking at Duane H's calendar db. I still
don't
know
what I'm doing!

I've created a query that brings back the fields I need. Here's the
concept: There are on-going volunteering opportunities
(VolunteerName)
associated with organizations (OrganizationName). Each event has a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek, 2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days of
the
week
with yes/no boxes). Each on-going volunteering opportunity has an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month that
shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and
Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in case
it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday, Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday,
Nz([NickName],[FirstName])
& "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID) INNER
JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[Duane Hookom]

You should be able to create a query and find the day on the calendar using:
TheDate: DateAdd("d", (WeekNo * 7) + (DayOfWk) - 8, #5/1/2005#)

This should allow to use the above expression with other fields as the
record source for the "day" subreport. Use a similar query but group by the
Sunday starting of each week calculated from "TheDate".


--
Duane Hookom
MS Access MVP

Okay, I think I have my subreport built:

OrganizationName (Shriner's)
ProgramName (Read with Kids)
Start and End (11:00 AM - 12:00 PM)

But I'm struggling with getting the information in the correct Date Box
(week, day). I'd appreciate any suggestions.

I would create a related table with one record per each opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3 and be for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP

Thanks for the tips. I like the idea of NumPer and DateIncr, and I
added
them
to my Volunteering table (getting rid of my previous frequency and day
fields).

However, I do need to know what days of the week are involoved and
which
week of the month. Our opportunities read as: 1st Monday of the month;
2nd
and 4th Wednesday of the month; the 5th Tuesday of the month. I would
appreciate suggestions on how to handle these requirements in my
tables.

I'm hoping for a report that I will give to our volunteers showing all
of
the volunteer opportunities avaialable to them for a generic 5-week
month
(essentially 35 day boxes: Sunday to Saturday across the top, 5 rows of
weeks). If a volunteer opportunity is on the 2nd and 4th Wednesday of
every
month, I'd like the report to have the volunteer information on the 2nd
Wednesday (11th box position) and the 4th Wednesday (25th box
position).

I apologize, I thought I had explained what fields I'd like to see on
the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks involoved
and
which week of the month
(that will help the opportunities are tied together- the volunteer
won't
just think they'll volunteer on the 2nd Wednesday without realizing
that
the
4th Wednesday is part of the same volunteering opportunity; or that
they
volunteer on Tuesday one week when the opportunity is Tuesday and
Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can help.



:

I don't agree with your structure or your method for storing the "x".
Values
like 3xWeek and 2xMonth have no utility value. You would be better of
with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week would be
12
times per month.

It would really be help if you would take the time to type a few
records
(significant fields only) and how you would expect to see these in a
report.

--
Duane Hookom
MS Access MVP


Duane,
I was having difficulty with Days and Frequency, so someone in the
Design
group directed me toward 7 check boxes. If I have a frequency of
2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me to
choose
one of the days. I thought the check box method didn't seem
"regulation"
but
it's working so far, but it sounds as though I should change it to
help
with
the calendar design (and I need all the "normal" I can get!). I'd
appreciate
any suggestions. I originally had Frequency FK in Volunteering
table
and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes) to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the
calendar
report, I'd show this "event" every week and occurring on Wed. and
Fri.

Hope that explains a bit better about where I'm trying to go.
Thanks.
I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID


:

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are
storing
in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

Hi there! I've been looking at Duane H's calendar db. I still
don't
know
what I'm doing!

I've created a query that brings back the fields I need. Here's
the
concept: There are on-going volunteering opportunities
(VolunteerName)
associated with organizations (OrganizationName). Each event has
a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek,
2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days
of
the
week
with yes/no boxes). Each on-going volunteering opportunity has
an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month
that
shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and
Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in
case
it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday,
Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday,
Nz([NickName],[FirstName])
& "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID)
INNER
JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[Stephanie]

Duane,
Thanks. I am a bit confused- I don't want any dates on the "calendar", just
generic weeks and days of the week. I'm trying to build a matrix of 35 "day
boxes":

(WeekNo/DaysID) Sunday Monday Tuesday...
1 VolOpp1
2 VoluOpp2
3 VolOpp1
4
5

where each Volunteer Opportunity has fields WeekNo and DayID associated in
the Volunteering table. And even if there is no Volunteer Opportunity
associated with a WeekNo/DayID, I want a blank "day box".

I built a Days table: DaysID, Days so that the user can use a combo box to
choose days of the week.

This is a query that brings back the VolunteerName (Read with Kids) and the
associated WeekNo and DaysID:
SELECT DISTINCT Volunteering.VolunteerName, FrequencyDays.WeekNo,
FrequencyDays.DayID
FROM (Event INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN FrequencyDays ON
Volunteering.VolunteeringID = FrequencyDays.VolunteeringID;

So this gives me:
Read with Kids, WeekNo=1, DaysID=1
Read with Kids, WeekNo=3, DaysID=1
Dog Training, WeekNo=2, DaysID=2

Hope that makes sense and can be done. Any suggestions?

You should be able to create a query and find the day on the calendar using:
TheDate: DateAdd("d", (WeekNo * 7) + (DayOfWk) - 8, #5/1/2005#)

This should allow to use the above expression with other fields as the
record source for the "day" subreport. Use a similar query but group by the
Sunday starting of each week calculated from "TheDate".


--
Duane Hookom
MS Access MVP

Okay, I think I have my subreport built:

OrganizationName (Shriner's)
ProgramName (Read with Kids)
Start and End (11:00 AM - 12:00 PM)

But I'm struggling with getting the information in the correct Date Box
(week, day). I'd appreciate any suggestions.

I would create a related table with one record per each opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3 and be for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP


Thanks for the tips. I like the idea of NumPer and DateIncr, and I
added
them
to my Volunteering table (getting rid of my previous frequency and day
fields).

However, I do need to know what days of the week are involoved and
which
week of the month. Our opportunities read as: 1st Monday of the month;
2nd
and 4th Wednesday of the month; the 5th Tuesday of the month. I would
appreciate suggestions on how to handle these requirements in my
tables.

I'm hoping for a report that I will give to our volunteers showing all
of
the volunteer opportunities avaialable to them for a generic 5-week
month
(essentially 35 day boxes: Sunday to Saturday across the top, 5 rows of
weeks). If a volunteer opportunity is on the 2nd and 4th Wednesday of
every
month, I'd like the report to have the volunteer information on the 2nd
Wednesday (11th box position) and the 4th Wednesday (25th box
position).

I apologize, I thought I had explained what fields I'd like to see on
the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks involoved
and
which week of the month
(that will help the opportunities are tied together- the volunteer
won't
just think they'll volunteer on the 2nd Wednesday without realizing
that
the
4th Wednesday is part of the same volunteering opportunity; or that
they
volunteer on Tuesday one week when the opportunity is Tuesday and
Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can help.



:

I don't agree with your structure or your method for storing the "x".
Values
like 3xWeek and 2xMonth have no utility value. You would be better of
with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week would be
12
times per month.

It would really be help if you would take the time to type a few
records
(significant fields only) and how you would expect to see these in a
report.

--
Duane Hookom
MS Access MVP


Duane,
I was having difficulty with Days and Frequency, so someone in the
Design
group directed me toward 7 check boxes. If I have a frequency of
2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me to
choose
one of the days. I thought the check box method didn't seem
"regulation"
but
it's working so far, but it sounds as though I should change it to
help
with
the calendar design (and I need all the "normal" I can get!). I'd
appreciate
any suggestions. I originally had Frequency FK in Volunteering
table
and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes) to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the
calendar
report, I'd show this "event" every week and occurring on Wed. and
Fri.

Hope that explains a bit better about where I'm trying to go.
Thanks.
I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID


:

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are
storing
in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

Hi there! I've been looking at Duane H's calendar db. I still
don't
know
what I'm doing!

I've created a query that brings back the fields I need. Here's
the
concept: There are on-going volunteering opportunities
(VolunteerName)
associated with organizations (OrganizationName). Each event has
a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek,
2xweek,
2xmonth...), and the Days associated (I ended up using the 7 days
of
the
week
with yes/no boxes). Each on-going volunteering opportunity has
an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month
that
shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and
Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query in
case
it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday, Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday,
Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday,
Nz([NickName],[FirstName])
& "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID)
INNER
JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[Duane Hookom]

You need to get the records number so you can display the required
information in the report. Don't display information that you don't want to
display. I used May of 2005 because the first day of May is a Sunday.

--
Duane Hookom
MS Access MVP

Duane,
Thanks. I am a bit confused- I don't want any dates on the "calendar",
just
generic weeks and days of the week. I'm trying to build a matrix of 35
"day
boxes":

(WeekNo/DaysID) Sunday Monday Tuesday...
1 VolOpp1
2 VoluOpp2
3 VolOpp1
4
5

where each Volunteer Opportunity has fields WeekNo and DayID associated in
the Volunteering table. And even if there is no Volunteer Opportunity
associated with a WeekNo/DayID, I want a blank "day box".

I built a Days table: DaysID, Days so that the user can use a combo box to
choose days of the week.

This is a query that brings back the VolunteerName (Read with Kids) and
the
associated WeekNo and DaysID:
SELECT DISTINCT Volunteering.VolunteerName, FrequencyDays.WeekNo,
FrequencyDays.DayID
FROM (Event INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN FrequencyDays ON
Volunteering.VolunteeringID = FrequencyDays.VolunteeringID;

So this gives me:
Read with Kids, WeekNo=1, DaysID=1
Read with Kids, WeekNo=3, DaysID=1
Dog Training, WeekNo=2, DaysID=2

Hope that makes sense and can be done. Any suggestions?

You should be able to create a query and find the day on the calendar
using:
TheDate: DateAdd("d", (WeekNo * 7) + (DayOfWk) - 8, #5/1/2005#)

This should allow to use the above expression with other fields as the
record source for the "day" subreport. Use a similar query but group by
the
Sunday starting of each week calculated from "TheDate".


--
Duane Hookom
MS Access MVP

Okay, I think I have my subreport built:

OrganizationName (Shriner's)
ProgramName (Read with Kids)
Start and End (11:00 AM - 12:00 PM)

But I'm struggling with getting the information in the correct Date Box
(week, day). I'd appreciate any suggestions.


:

I would create a related table with one record per each opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3 and be
for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP


Thanks for the tips. I like the idea of NumPer and DateIncr, and I
added
them
to my Volunteering table (getting rid of my previous frequency and
day
fields).

However, I do need to know what days of the week are involoved and
which
week of the month. Our opportunities read as: 1st Monday of the
month;
2nd
and 4th Wednesday of the month; the 5th Tuesday of the month. I
would
appreciate suggestions on how to handle these requirements in my
tables.

I'm hoping for a report that I will give to our volunteers showing
all
of
the volunteer opportunities avaialable to them for a generic 5-week
month
(essentially 35 day boxes: Sunday to Saturday across the top, 5 rows
of
weeks). If a volunteer opportunity is on the 2nd and 4th Wednesday
of
every
month, I'd like the report to have the volunteer information on the
2nd
Wednesday (11th box position) and the 4th Wednesday (25th box
position).

I apologize, I thought I had explained what fields I'd like to see
on
the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks
involoved
and
which week of the month
(that will help the opportunities are tied together- the volunteer
won't
just think they'll volunteer on the 2nd Wednesday without realizing
that
the
4th Wednesday is part of the same volunteering opportunity; or that
they
volunteer on Tuesday one week when the opportunity is Tuesday and
Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can help.



:

I don't agree with your structure or your method for storing the
"x".
Values
like 3xWeek and 2xMonth have no utility value. You would be better
of
with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week would
be
12
times per month.

It would really be help if you would take the time to type a few
records
(significant fields only) and how you would expect to see these in
a
report.

--
Duane Hookom
MS Access MVP


Duane,
I was having difficulty with Days and Frequency, so someone in
the
Design
group directed me toward 7 check boxes. If I have a frequency of
2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed me
to
choose
one of the days. I thought the check box method didn't seem
"regulation"
but
it's working so far, but it sounds as though I should change it
to
help
with
the calendar design (and I need all the "normal" I can get!).
I'd
appreciate
any suggestions. I originally had Frequency FK in Volunteering
table
and
Days FK in Frequency. But now I have moved Days (7 yes/no boxes)
to
Volunteering. That probably doesn't make sense.

Here's a sample record from my query:
EventStart 11:00PM
EventEnd 12:00 PM
OrganizationName Shriner's Hospital
ProgramName Reading with kids
Frequency 2xWeek
Days: somehow I want to say Wednesday and Friday, so that on the
calendar
report, I'd show this "event" every week and occurring on Wed.
and
Fri.

Hope that explains a bit better about where I'm trying to go.
Thanks.
I'll
list a bit about my tables as well:

Contacts
ContactID
(info about my volunteers)

Volunteering
VolunteeringID
VolunteerName (Reading with Kids)
FrequencyID
Monday (yes/no box for each day of the week)

EventSponsor (CPK)
VolunteeringID
OrganizationID

Organizations
OrganizationID
OrganizationName (Shriner's Hospital)

Event
EventVolunteeringID
ContactID
VolunteeringID


:

Duane's calendar reports use normalized data, not 7 check boxes.

It's difficult to understand what kind of information you are
storing
in
your tables without seeing sample records.

--
Duane Hookom
MS Access MVP
--

message
Hi there! I've been looking at Duane H's calendar db. I
still
don't
know
what I'm doing!

I've created a query that brings back the fields I need.
Here's
the
concept: There are on-going volunteering opportunities
(VolunteerName)
associated with organizations (OrganizationName). Each event
has
a
beginning
and ending time (EventStart, EventEnd), a frequency (1xweek,
2xweek,
2xmonth...), and the Days associated (I ended up using the 7
days
of
the
week
with yes/no boxes). Each on-going volunteering opportunity
has
an
associated
Coordinator.

What I'd like to accomplish is a generic 5 week calendar month
that
shows
the OrganizationName, VolunteerName, EventStart, EventEnd, and
Coordinator
to
appear on the correct Day and with the appropriate Frequency.

I'm appreciate any starting suggestions! I'll post my query
in
case
it
helps. Thanks- I appreciate the Calendar pro's help!

SELECT DISTINCT Volunteering.VolunteeEventStart,
Volunteering.VolunteerEventEnd, Volunteering.VolunteeringID,
Organizations.OrganizationName, Volunteering.VolunteerName,
Frequency.Frequency, Volunteering.Monday,
Volunteering.Tuesday,
Volunteering.Wednesday, Volunteering.Thursday,
Volunteering.Friday,
Volunteering.Saturday, Volunteering.Sunday,
Nz([NickName],[FirstName])
& "
"
& [LastName] AS [Member Name], Event.Coordinator
FROM ((Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON
Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER
JOIN
EventSponsors ON Organizations.OrganizationID =
EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID)
INNER
JOIN
Contacts ON Event.ContactID = Contacts.ContactID
WHERE (((Volunteering.VolunteerOngoing)=Yes) AND
((Event.Coordinator)=Yes));

 

[aaearhart]

Hey, Mr. Hookom-

Where can a kid like me score a copy of this calendaring tool?

Thanks muchly!
/amelia

 

[Stephanie]

Duane,
Thanks for the explanation. As I understand it
TheDate: DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) with WeekNo=1 and
DayID=2, gives me 1, which is 5/1/05 (1 and 5/1/05 are both Mondays). I think
that makes sense.

At the moment, with your equation, I'm returning dates in May 05 where the
day corresponds to the correct DayID. However, I believe I need to return
the corresponding Sunday of that week in order to group for the week.

In your rptCalendar, you used
WeekOf: DateAdd("d",-Weekday([SchedDate]),[schedDate])+1
to get the correct Sunday.

However, every iteration I've tried with TheDate:
DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) to get to a Sunday gives me a
lovely date in the 1800's. And rather than giving me all Sundays, I'm still
getting the actual date based on WeekNo and DayID, just a couple of centuries
earlier.

Sorry to be such a pain. Thanks

 

[Duane Hookom]

I created a table with fields WeekNo and DayID and added records
1 2
1 4
3 6
2 5
I used your expression to calculate a date value that could be used in the
calendar report and I got:
5/2/2005
5/4/2005
5/20/2005
5/12/2005

This is exactly what I would expect and would provide an adequate recordset
for use with the calendar report. Just hide the month name as well as the
"days".

--
Duane Hookom
MS Access MVP
--

Duane,
Thanks for the explanation. As I understand it
TheDate: DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) with WeekNo=1 and
DayID=2, gives me 1, which is 5/1/05 (1 and 5/1/05 are both Mondays). I
think
that makes sense.

At the moment, with your equation, I'm returning dates in May 05 where the
day corresponds to the correct DayID. However, I believe I need to return
the corresponding Sunday of that week in order to group for the week.

In your rptCalendar, you used
WeekOf: DateAdd("d",-Weekday([SchedDate]),[schedDate])+1
to get the correct Sunday.

However, every iteration I've tried with TheDate:
DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) to get to a Sunday gives me
a
lovely date in the 1800's. And rather than giving me all Sundays, I'm
still
getting the actual date based on WeekNo and DayID, just a couple of
centuries
earlier.

Sorry to be such a pain. Thanks

You need to get the records number so you can display the required
information in the report. Don't display information that you don't want
to
display. I used May of 2005 because the first day of May is a Sunday.

 

[Duane Hookom]

http://www.invisibleinc.com/divFiles.cfm?divDivID=4

 

[Stephanie]

Thanks for the litmus test.
It's getting better! I have WeekNo working as a column down the left side
of the report and the Volunteer Opportunities show up in the correct week row.

However, I'm still struggling with TheDay. I've fixed the field display to
only show the day of the week, which is fine. However, to get the Days in
the correct order, I had to start with [TheDate]-4 which gives me Sunday May
1, 2005 (which I've trimmed down to "Sunday"). Is it OK that I had to use
[TheDate]-4 to start or did I do something wrong? Then I follow up with
[TheDate]-3 to get Monday...

But my Volunteer Opportunities are not showing up in the correct Day
"column". I'm using my srptCalendar which shows the OrganizationName,
VolunteerName and Start/End Times. I've linked srptCalendar on WeekNo;DayID.
All of the volunteer opportunities appear in the correct week, but not the
correct day. I tried adding the srptCalendar under each Day heading, but
then I just get all of the Volunteer Opportunities over and over again.
Where did I go wrong? Thanks for your patience.

I created a table with fields WeekNo and DayID and added records
1 2
1 4
3 6
2 5
I used your expression to calculate a date value that could be used in the
calendar report and I got:
5/2/2005
5/4/2005
5/20/2005
5/12/2005

This is exactly what I would expect and would provide an adequate recordset
for use with the calendar report. Just hide the month name as well as the
"days".

--
Duane Hookom
MS Access MVP
--

Duane,
Thanks for the explanation. As I understand it
TheDate: DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) with WeekNo=1 and
DayID=2, gives me 1, which is 5/1/05 (1 and 5/1/05 are both Mondays). I
think
that makes sense.

At the moment, with your equation, I'm returning dates in May 05 where the
day corresponds to the correct DayID. However, I believe I need to return
the corresponding Sunday of that week in order to group for the week.

In your rptCalendar, you used
WeekOf: DateAdd("d",-Weekday([SchedDate]),[schedDate])+1
to get the correct Sunday.

However, every iteration I've tried with TheDate:
DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) to get to a Sunday gives me
a
lovely date in the 1800's. And rather than giving me all Sundays, I'm
still
getting the actual date based on WeekNo and DayID, just a couple of
centuries
earlier.

Sorry to be such a pain. Thanks

You need to get the records number so you can display the required
information in the report. Don't display information that you don't want
to
display. I used May of 2005 because the first day of May is a Sunday.

--
Duane Hookom
MS Access MVP


Duane,
Thanks. I am a bit confused- I don't want any dates on the "calendar",
just
generic weeks and days of the week. I'm trying to build a matrix of 35
"day
boxes":

(WeekNo/DaysID) Sunday Monday Tuesday...
1 VolOpp1
2 VoluOpp2
3 VolOpp1
4
5

where each Volunteer Opportunity has fields WeekNo and DayID associated
in
the Volunteering table. And even if there is no Volunteer Opportunity
associated with a WeekNo/DayID, I want a blank "day box".

I built a Days table: DaysID, Days so that the user can use a combo box
to
choose days of the week.

This is a query that brings back the VolunteerName (Read with Kids) and
the
associated WeekNo and DaysID:
SELECT DISTINCT Volunteering.VolunteerName, FrequencyDays.WeekNo,
FrequencyDays.DayID
FROM (Event INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN FrequencyDays ON
Volunteering.VolunteeringID = FrequencyDays.VolunteeringID;

So this gives me:
Read with Kids, WeekNo=1, DaysID=1
Read with Kids, WeekNo=3, DaysID=1
Dog Training, WeekNo=2, DaysID=2

Hope that makes sense and can be done. Any suggestions?


:

You should be able to create a query and find the day on the calendar
using:
TheDate: DateAdd("d", (WeekNo * 7) + (DayOfWk) - 8, #5/1/2005#)

This should allow to use the above expression with other fields as the
record source for the "day" subreport. Use a similar query but group
by
the
Sunday starting of each week calculated from "TheDate".


--
Duane Hookom
MS Access MVP


Okay, I think I have my subreport built:

OrganizationName (Shriner's)
ProgramName (Read with Kids)
Start and End (11:00 AM - 12:00 PM)

But I'm struggling with getting the information in the correct Date
Box
(week, day). I'd appreciate any suggestions.


:

I would create a related table with one record per each
opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3 and
be
for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP


Thanks for the tips. I like the idea of NumPer and DateIncr, and
I
added
them
to my Volunteering table (getting rid of my previous frequency
and
day
fields).

However, I do need to know what days of the week are involoved
and
which
week of the month. Our opportunities read as: 1st Monday of the
month;
2nd
and 4th Wednesday of the month; the 5th Tuesday of the month. I
would
appreciate suggestions on how to handle these requirements in my
tables.

I'm hoping for a report that I will give to our volunteers
showing
all
of
the volunteer opportunities avaialable to them for a generic
5-week
month
(essentially 35 day boxes: Sunday to Saturday across the top, 5
rows
of
weeks). If a volunteer opportunity is on the 2nd and 4th
Wednesday
of
every
month, I'd like the report to have the volunteer information on
the
2nd
Wednesday (11th box position) and the 4th Wednesday (25th box
position).

I apologize, I thought I had explained what fields I'd like to
see
on
the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks
involoved
and
which week of the month
(that will help the opportunities are tied together- the
volunteer
won't
just think they'll volunteer on the 2nd Wednesday without
realizing
that
the
4th Wednesday is part of the same volunteering opportunity; or
that
they
volunteer on Tuesday one week when the opportunity is Tuesday and
Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can help.



:

I don't agree with your structure or your method for storing the
"x".
Values
like 3xWeek and 2xMonth have no utility value. You would be
better
of
with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week
would
be
12
times per month.

It would really be help if you would take the time to type a few
records
(significant fields only) and how you would expect to see these
in
a
report.

--
Duane Hookom
MS Access MVP


message
Duane,
I was having difficulty with Days and Frequency, so someone in
the
Design
group directed me toward 7 check boxes. If I have a frequency
of
2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only allowed
me
to
choose
one of the days. I thought the check box method didn't seem
"regulation"
but
it's working so far, but it sounds as though I should change

 

[Duane Hookom]

Actually, you could probably just create a table of weeks
tblWeeks
WeekNo
1
2
3
4
5
Use this table as the only record source for you main report.
Add 7 text boxes to a header section with control sources like:
=1
=2
=3
=4
=5
=6
=7
Name these like txt1, txt2,...txt7
Set the link master child like
first day subreport
Link Master: WeekNo, txt1
Link Child: WeekNo, DayID
second day subreport
Link Master: WeekNo, txt2
Link Child: WeekNo, DayID
third day subreport
Link Master: WeekNo, txt3
Link Child: WeekNo, DayID
etc.


--
Duane Hookom
MS Access MVP

Thanks for the litmus test.
It's getting better! I have WeekNo working as a column down the left side
of the report and the Volunteer Opportunities show up in the correct week
row.

However, I'm still struggling with TheDay. I've fixed the field display
to
only show the day of the week, which is fine. However, to get the Days in
the correct order, I had to start with [TheDate]-4 which gives me Sunday
May
1, 2005 (which I've trimmed down to "Sunday"). Is it OK that I had to use
[TheDate]-4 to start or did I do something wrong? Then I follow up with
[TheDate]-3 to get Monday...

But my Volunteer Opportunities are not showing up in the correct Day
"column". I'm using my srptCalendar which shows the OrganizationName,
VolunteerName and Start/End Times. I've linked srptCalendar on
WeekNo;DayID.
All of the volunteer opportunities appear in the correct week, but not the
correct day. I tried adding the srptCalendar under each Day heading, but
then I just get all of the Volunteer Opportunities over and over again.
Where did I go wrong? Thanks for your patience.

I created a table with fields WeekNo and DayID and added records
1 2
1 4
3 6
2 5
I used your expression to calculate a date value that could be used in
the
calendar report and I got:
5/2/2005
5/4/2005
5/20/2005
5/12/2005

This is exactly what I would expect and would provide an adequate
recordset
for use with the calendar report. Just hide the month name as well as the
"days".

--
Duane Hookom
MS Access MVP
--

Duane,
Thanks for the explanation. As I understand it
TheDate: DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) with WeekNo=1
and
DayID=2, gives me 1, which is 5/1/05 (1 and 5/1/05 are both Mondays). I
think
that makes sense.

At the moment, with your equation, I'm returning dates in May 05 where
the
day corresponds to the correct DayID. However, I believe I need to
return
the corresponding Sunday of that week in order to group for the week.

In your rptCalendar, you used
WeekOf: DateAdd("d",-Weekday([SchedDate]),[schedDate])+1
to get the correct Sunday.

However, every iteration I've tried with TheDate:
DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) to get to a Sunday gives
me
a
lovely date in the 1800's. And rather than giving me all Sundays, I'm
still
getting the actual date based on WeekNo and DayID, just a couple of
centuries
earlier.

Sorry to be such a pain. Thanks


:

You need to get the records number so you can display the required
information in the report. Don't display information that you don't
want
to
display. I used May of 2005 because the first day of May is a Sunday.

--
Duane Hookom
MS Access MVP


Duane,
Thanks. I am a bit confused- I don't want any dates on the
"calendar",
just
generic weeks and days of the week. I'm trying to build a matrix of
35
"day
boxes":

(WeekNo/DaysID) Sunday Monday Tuesday...
1 VolOpp1
2 VoluOpp2
3 VolOpp1
4
5

where each Volunteer Opportunity has fields WeekNo and DayID
associated
in
the Volunteering table. And even if there is no Volunteer
Opportunity
associated with a WeekNo/DayID, I want a blank "day box".

I built a Days table: DaysID, Days so that the user can use a combo
box
to
choose days of the week.

This is a query that brings back the VolunteerName (Read with Kids)
and
the
associated WeekNo and DaysID:
SELECT DISTINCT Volunteering.VolunteerName, FrequencyDays.WeekNo,
FrequencyDays.DayID
FROM (Event INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN FrequencyDays ON
Volunteering.VolunteeringID = FrequencyDays.VolunteeringID;

So this gives me:
Read with Kids, WeekNo=1, DaysID=1
Read with Kids, WeekNo=3, DaysID=1
Dog Training, WeekNo=2, DaysID=2

Hope that makes sense and can be done. Any suggestions?


:

You should be able to create a query and find the day on the
calendar
using:
TheDate: DateAdd("d", (WeekNo * 7) + (DayOfWk) - 8, #5/1/2005#)

This should allow to use the above expression with other fields as
the
record source for the "day" subreport. Use a similar query but
group
by
the
Sunday starting of each week calculated from "TheDate".


--
Duane Hookom
MS Access MVP


Okay, I think I have my subreport built:

OrganizationName (Shriner's)
ProgramName (Read with Kids)
Start and End (11:00 AM - 12:00 PM)

But I'm struggling with getting the information in the correct
Date
Box
(week, day). I'd appreciate any suggestions.


:

I would create a related table with one record per each
opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3
and
be
for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP


message
Thanks for the tips. I like the idea of NumPer and DateIncr,
and
I
added
them
to my Volunteering table (getting rid of my previous frequency
and
day
fields).

However, I do need to know what days of the week are involoved
and
which
week of the month. Our opportunities read as: 1st Monday of
the
month;
2nd
and 4th Wednesday of the month; the 5th Tuesday of the month.
I
would
appreciate suggestions on how to handle these requirements in
my
tables.

I'm hoping for a report that I will give to our volunteers
showing
all
of
the volunteer opportunities avaialable to them for a generic
5-week
month
(essentially 35 day boxes: Sunday to Saturday across the top,
5
rows
of
weeks). If a volunteer opportunity is on the 2nd and 4th
Wednesday
of
every
month, I'd like the report to have the volunteer information
on
the
2nd
Wednesday (11th box position) and the 4th Wednesday (25th box
position).

I apologize, I thought I had explained what fields I'd like to
see
on
the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks
involoved
and
which week of the month
(that will help the opportunities are tied together- the
volunteer
won't
just think they'll volunteer on the 2nd Wednesday without
realizing
that
the
4th Wednesday is part of the same volunteering opportunity; or
that
they
volunteer on Tuesday one week when the opportunity is Tuesday
and
Thursday).

Examples-
Boxes 11 and 25:
Shriner's Hospital
Read with Kids
11:00 AM - 12:00 PM
2nd and 4th Wednesday

Box 5:
Boy's Detention Facility
Dog Training
1:00 PM - 2:30 PM
1st Thursday

Boxes 3, 10, 17, 24, 31:
Primay Children's Hospital
Physical Therapy
9:00 AM - 10:00 AM
Every Tuesday

Hope that explains what I'm looking for and I hope you can
help.



:

I don't agree with your structure or your method for storing
the
"x".
Values
like 3xWeek and 2xMonth have no utility value. You would be
better
of
with
two fields
NumPer DateIncr
3 Week
2 Month
or a single field with times per month where 3 times per week
would
be
12
times per month.

It would really be help if you would take the time to type a
few
records
(significant fields only) and how you would expect to see
these
in
a
report.

--
Duane Hookom
MS Access MVP


message
Duane,
I was having difficulty with Days and Frequency, so someone
in
the
Design
group directed me toward 7 check boxes. If I have a
frequency
of
2xWeek,
I
want to be able to choose Monday and Wednesday.

I originally had a combo box for days, but that only
allowed
me
to
choose
one of the days. I thought the check box method didn't
seem
"regulation"
but
it's working so far, but it sounds as though I should
change

 

[Stephanie]

Okay, I'm following along.
I created tblWeeks and have it as my record source of rptCalendar. In the
header of rptCalendar, I created 7 text boxes: txt1 with control source =1...
txt7 with control source=7. I put WeekNo in the deatil section. Now I have
a lovely matrix of weeks and days. So that's my main report: rptCalendar.

I'm a bit confused about the subreport. Am I still using my srptCalendar
that lists:
OrganizationName, VolunteerName, StartTime, EndTime? The only "links"
available are WeekNo, DayID, and VolunteeringID. I linked both child and
master as WeekNo. That gave me the volunteer opportunities on the correct
week but not on the correct day. Then I realized that manually I could "fix"
the links, so I have:
Link Child: WeekNo;DayID
Link Master: WeekNo;txt1
which brings back no volunteer opportunities.

Did I somehow mess up my srptCalendar, or am I using something different
than you thought? Thanks.

Actually, you could probably just create a table of weeks
tblWeeks
WeekNo
1
2
3
4
5
Use this table as the only record source for you main report.
Add 7 text boxes to a header section with control sources like:
=1
=2
=3
=4
=5
=6
=7
Name these like txt1, txt2,...txt7
Set the link master child like
first day subreport
Link Master: WeekNo, txt1
Link Child: WeekNo, DayID
second day subreport
Link Master: WeekNo, txt2
Link Child: WeekNo, DayID
third day subreport
Link Master: WeekNo, txt3
Link Child: WeekNo, DayID
etc.


--
Duane Hookom
MS Access MVP

Thanks for the litmus test.
It's getting better! I have WeekNo working as a column down the left side
of the report and the Volunteer Opportunities show up in the correct week
row.

However, I'm still struggling with TheDay. I've fixed the field display
to
only show the day of the week, which is fine. However, to get the Days in
the correct order, I had to start with [TheDate]-4 which gives me Sunday
May
1, 2005 (which I've trimmed down to "Sunday"). Is it OK that I had to use
[TheDate]-4 to start or did I do something wrong? Then I follow up with
[TheDate]-3 to get Monday...

But my Volunteer Opportunities are not showing up in the correct Day
"column". I'm using my srptCalendar which shows the OrganizationName,
VolunteerName and Start/End Times. I've linked srptCalendar on
WeekNo;DayID.
All of the volunteer opportunities appear in the correct week, but not the
correct day. I tried adding the srptCalendar under each Day heading, but
then I just get all of the Volunteer Opportunities over and over again.
Where did I go wrong? Thanks for your patience.

I created a table with fields WeekNo and DayID and added records
1 2
1 4
3 6
2 5
I used your expression to calculate a date value that could be used in
the
calendar report and I got:
5/2/2005
5/4/2005
5/20/2005
5/12/2005

This is exactly what I would expect and would provide an adequate
recordset
for use with the calendar report. Just hide the month name as well as the
"days".

--
Duane Hookom
MS Access MVP
--

Duane,
Thanks for the explanation. As I understand it
TheDate: DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) with WeekNo=1
and
DayID=2, gives me 1, which is 5/1/05 (1 and 5/1/05 are both Mondays). I
think
that makes sense.

At the moment, with your equation, I'm returning dates in May 05 where
the
day corresponds to the correct DayID. However, I believe I need to
return
the corresponding Sunday of that week in order to group for the week.

In your rptCalendar, you used
WeekOf: DateAdd("d",-Weekday([SchedDate]),[schedDate])+1
to get the correct Sunday.

However, every iteration I've tried with TheDate:
DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) to get to a Sunday gives
me
a
lovely date in the 1800's. And rather than giving me all Sundays, I'm
still
getting the actual date based on WeekNo and DayID, just a couple of
centuries
earlier.

Sorry to be such a pain. Thanks


:

You need to get the records number so you can display the required
information in the report. Don't display information that you don't
want
to
display. I used May of 2005 because the first day of May is a Sunday.

--
Duane Hookom
MS Access MVP


Duane,
Thanks. I am a bit confused- I don't want any dates on the
"calendar",
just
generic weeks and days of the week. I'm trying to build a matrix of
35
"day
boxes":

(WeekNo/DaysID) Sunday Monday Tuesday...
1 VolOpp1
2 VoluOpp2
3 VolOpp1
4
5

where each Volunteer Opportunity has fields WeekNo and DayID
associated
in
the Volunteering table. And even if there is no Volunteer
Opportunity
associated with a WeekNo/DayID, I want a blank "day box".

I built a Days table: DaysID, Days so that the user can use a combo
box
to
choose days of the week.

This is a query that brings back the VolunteerName (Read with Kids)
and
the
associated WeekNo and DaysID:
SELECT DISTINCT Volunteering.VolunteerName, FrequencyDays.WeekNo,
FrequencyDays.DayID
FROM (Event INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN FrequencyDays ON
Volunteering.VolunteeringID = FrequencyDays.VolunteeringID;

So this gives me:
Read with Kids, WeekNo=1, DaysID=1
Read with Kids, WeekNo=3, DaysID=1
Dog Training, WeekNo=2, DaysID=2

Hope that makes sense and can be done. Any suggestions?


:

You should be able to create a query and find the day on the
calendar
using:
TheDate: DateAdd("d", (WeekNo * 7) + (DayOfWk) - 8, #5/1/2005#)

This should allow to use the above expression with other fields as
the
record source for the "day" subreport. Use a similar query but
group
by
the
Sunday starting of each week calculated from "TheDate".


--
Duane Hookom
MS Access MVP


Okay, I think I have my subreport built:

OrganizationName (Shriner's)
ProgramName (Read with Kids)
Start and End (11:00 AM - 12:00 PM)

But I'm struggling with getting the information in the correct
Date
Box
(week, day). I'd appreciate any suggestions.


:

I would create a related table with one record per each
opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3
and
be
for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP


message
Thanks for the tips. I like the idea of NumPer and DateIncr,
and
I
added
them
to my Volunteering table (getting rid of my previous frequency
and
day
fields).

However, I do need to know what days of the week are involoved
and
which
week of the month. Our opportunities read as: 1st Monday of
the
month;
2nd
and 4th Wednesday of the month; the 5th Tuesday of the month.
I
would
appreciate suggestions on how to handle these requirements in
my
tables.

I'm hoping for a report that I will give to our volunteers
showing
all
of
the volunteer opportunities avaialable to them for a generic
5-week
month
(essentially 35 day boxes: Sunday to Saturday across the top,
5
rows
of
weeks). If a volunteer opportunity is on the 2nd and 4th
Wednesday
of
every
month, I'd like the report to have the volunteer information
on
the
2nd
Wednesday (11th box position) and the 4th Wednesday (25th box
position).

I apologize, I thought I had explained what fields I'd like to
see
on
the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks
involoved
and

 

[Stephanie]

No, really. I'm not too stupid to live. I think it's working! Once I put
all of the subreports into place with the correct txt# in the link. Let me
organize it better to make sure- I'll post back if anything strange is going
on.

I appreciate the kind way in which you share your talents. To be able to
figure out the issues long-distance if fairly amazing to me. Thanks.

Actually, you could probably just create a table of weeks
tblWeeks
WeekNo
1
2
3
4
5
Use this table as the only record source for you main report.
Add 7 text boxes to a header section with control sources like:
=1
=2
=3
=4
=5
=6
=7
Name these like txt1, txt2,...txt7
Set the link master child like
first day subreport
Link Master: WeekNo, txt1
Link Child: WeekNo, DayID
second day subreport
Link Master: WeekNo, txt2
Link Child: WeekNo, DayID
third day subreport
Link Master: WeekNo, txt3
Link Child: WeekNo, DayID
etc.


--
Duane Hookom
MS Access MVP

Thanks for the litmus test.
It's getting better! I have WeekNo working as a column down the left side
of the report and the Volunteer Opportunities show up in the correct week
row.

However, I'm still struggling with TheDay. I've fixed the field display
to
only show the day of the week, which is fine. However, to get the Days in
the correct order, I had to start with [TheDate]-4 which gives me Sunday
May
1, 2005 (which I've trimmed down to "Sunday"). Is it OK that I had to use
[TheDate]-4 to start or did I do something wrong? Then I follow up with
[TheDate]-3 to get Monday...

But my Volunteer Opportunities are not showing up in the correct Day
"column". I'm using my srptCalendar which shows the OrganizationName,
VolunteerName and Start/End Times. I've linked srptCalendar on
WeekNo;DayID.
All of the volunteer opportunities appear in the correct week, but not the
correct day. I tried adding the srptCalendar under each Day heading, but
then I just get all of the Volunteer Opportunities over and over again.
Where did I go wrong? Thanks for your patience.

I created a table with fields WeekNo and DayID and added records
1 2
1 4
3 6
2 5
I used your expression to calculate a date value that could be used in
the
calendar report and I got:
5/2/2005
5/4/2005
5/20/2005
5/12/2005

This is exactly what I would expect and would provide an adequate
recordset
for use with the calendar report. Just hide the month name as well as the
"days".

--
Duane Hookom
MS Access MVP
--

Duane,
Thanks for the explanation. As I understand it
TheDate: DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) with WeekNo=1
and
DayID=2, gives me 1, which is 5/1/05 (1 and 5/1/05 are both Mondays). I
think
that makes sense.

At the moment, with your equation, I'm returning dates in May 05 where
the
day corresponds to the correct DayID. However, I believe I need to
return
the corresponding Sunday of that week in order to group for the week.

In your rptCalendar, you used
WeekOf: DateAdd("d",-Weekday([SchedDate]),[schedDate])+1
to get the correct Sunday.

However, every iteration I've tried with TheDate:
DateAdd("d",([WeekNo]*7)+([DayID])-8,#5/1/05#) to get to a Sunday gives
me
a
lovely date in the 1800's. And rather than giving me all Sundays, I'm
still
getting the actual date based on WeekNo and DayID, just a couple of
centuries
earlier.

Sorry to be such a pain. Thanks


:

You need to get the records number so you can display the required
information in the report. Don't display information that you don't
want
to
display. I used May of 2005 because the first day of May is a Sunday.

--
Duane Hookom
MS Access MVP


Duane,
Thanks. I am a bit confused- I don't want any dates on the
"calendar",
just
generic weeks and days of the week. I'm trying to build a matrix of
35
"day
boxes":

(WeekNo/DaysID) Sunday Monday Tuesday...
1 VolOpp1
2 VoluOpp2
3 VolOpp1
4
5

where each Volunteer Opportunity has fields WeekNo and DayID
associated
in
the Volunteering table. And even if there is no Volunteer
Opportunity
associated with a WeekNo/DayID, I want a blank "day box".

I built a Days table: DaysID, Days so that the user can use a combo
box
to
choose days of the week.

This is a query that brings back the VolunteerName (Read with Kids)
and
the
associated WeekNo and DaysID:
SELECT DISTINCT Volunteering.VolunteerName, FrequencyDays.WeekNo,
FrequencyDays.DayID
FROM (Event INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID) INNER JOIN FrequencyDays ON
Volunteering.VolunteeringID = FrequencyDays.VolunteeringID;

So this gives me:
Read with Kids, WeekNo=1, DaysID=1
Read with Kids, WeekNo=3, DaysID=1
Dog Training, WeekNo=2, DaysID=2

Hope that makes sense and can be done. Any suggestions?


:

You should be able to create a query and find the day on the
calendar
using:
TheDate: DateAdd("d", (WeekNo * 7) + (DayOfWk) - 8, #5/1/2005#)

This should allow to use the above expression with other fields as
the
record source for the "day" subreport. Use a similar query but
group
by
the
Sunday starting of each week calculated from "TheDate".


--
Duane Hookom
MS Access MVP


Okay, I think I have my subreport built:

OrganizationName (Shriner's)
ProgramName (Read with Kids)
Start and End (11:00 AM - 12:00 PM)

But I'm struggling with getting the information in the correct
Date
Box
(week, day). I'd appreciate any suggestions.


:

I would create a related table with one record per each
opportunity.

VolunteeringID WeekNo DayOfWk
3 1 3
3 3 2
3 3 4
The above would be for the volunteering record with an ID of 3
and
be
for
Week Number 1, Day 3 (Tuesday)
Week Number 3, Day 2 (Monday)
Week Number 3, Day 4 (Wednesday)

--
Duane Hookom
MS Access MVP


message
Thanks for the tips. I like the idea of NumPer and DateIncr,
and
I
added
them
to my Volunteering table (getting rid of my previous frequency
and
day
fields).

However, I do need to know what days of the week are involoved
and
which
week of the month. Our opportunities read as: 1st Monday of
the
month;
2nd
and 4th Wednesday of the month; the 5th Tuesday of the month.
I
would
appreciate suggestions on how to handle these requirements in
my
tables.

I'm hoping for a report that I will give to our volunteers
showing
all
of
the volunteer opportunities avaialable to them for a generic
5-week
month
(essentially 35 day boxes: Sunday to Saturday across the top,
5
rows
of
weeks). If a volunteer opportunity is on the 2nd and 4th
Wednesday
of
every
month, I'd like the report to have the volunteer information
on
the
2nd
Wednesday (11th box position) and the 4th Wednesday (25th box
position).

I apologize, I thought I had explained what fields I'd like to
see
on
the
report. I need the calander box to show:

OrganizationName
ProgramName
EventStart
EventEnd
And then when the established correctly, the day of the weeks
involoved
and