Calendar report empty week problem

[Guest]

I have adapted Duane Hookum's great monthly calendar report available at
http://www.access.hookom.net/Samples.htm. The only changes are to the date
formats and adding in weekend days. As expected, the report does not show
weeks when there are no records - it jumps to the start of the next week
where there are records.

However: (1) I would like to show weeks for which there are currently no
records so that notes can be added manually to hard copies before being
transferred to the database. (2) As a natural extension to the above, I need
to be able to select a date range for the report, which may preceed the
earliest or follow the latest weeks for which there are records.

Any suggestions from people who'ved used/adapted this report (Duane?!) or
similar reports would be greatfully received!

Many thanks

 

[Guest]

The easiest method is to create a table with all weeks.
tblWeeks
WeekOf date/time

It may be easiest for you to create the weekof values in Excel and then
paste them into tblWeeks. You can the use start and end dates entered into
form controls to filter the calendar.

 

[Guest]

Thanks Duane,

I've done as suggested and created a tblWeekOf for several years into the
future! However I still can't get the calendars subforms to show 'empty'
records in weeks where there are no records. The query that supplies the data
for the main form looks like this now:

SELECT tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
FROM qryReports RIGHT JOIN tblWeekOf ON qryReports.Date = tblWeekOf.Date
GROUP BY tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
HAVING (((tblWeekOf.WeekOf) Between [Type the beginning date:] And [Type the
ending date:]));

Instead of Doctors I'm working with Shows and have temporarily used
parameters within the query for ease. This query seems to correctly throw up
all records within the parameter dates given (ie with WeekOf + IDShow + Show
Title) plus 'empty' records with just WeekOf data where there are no shows in
that week. qryReports provides the specific IDShow information.

The subreports are supplied by a query with all events and linked to the
main report by Child fields: Date;IDShow to Master fields: txtDay1;IDShow.

Its so nearly there, but can't quite get it to work! Anymore hints please?

Many thanks

 

[Guest]

Don't you mean "main report" rather than "main form"? Where does
"tblWeekOf.Date" come from? I thought the only field in tblWeekOf would be
[WeekOf]. Is qryReports.Date the same as WeekOf?

I would try something like the following for the record source of the main
report.

SELECT WeekOf, IDShow, ShowTitle
FROM tblWeekOf, tblShows
WHERE WeekOf Between Forms!frmRptDates!txtStartDate and
Forms!frmRptDates!txtEndDate;

This assumes you have a table where shows are unique. This would send all
weeks and shows to your report within a particular date range.
--
Duane Hookom
Microsoft Access MVP

Thanks Duane,

I've done as suggested and created a tblWeekOf for several years into the
future! However I still can't get the calendars subforms to show 'empty'
records in weeks where there are no records. The query that supplies the data
for the main form looks like this now:

SELECT tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
FROM qryReports RIGHT JOIN tblWeekOf ON qryReports.Date = tblWeekOf.Date
GROUP BY tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
HAVING (((tblWeekOf.WeekOf) Between [Type the beginning date:] And [Type the
ending date:]));

Instead of Doctors I'm working with Shows and have temporarily used
parameters within the query for ease. This query seems to correctly throw up
all records within the parameter dates given (ie with WeekOf + IDShow + Show
Title) plus 'empty' records with just WeekOf data where there are no shows in
that week. qryReports provides the specific IDShow information.

The subreports are supplied by a query with all events and linked to the
main report by Child fields: Date;IDShow to Master fields: txtDay1;IDShow.

Its so nearly there, but can't quite get it to work! Anymore hints please?

Many thanks

The easiest method is to create a table with all weeks.
tblWeeks
WeekOf date/time

It may be easiest for you to create the weekof values in Excel and then
paste them into tblWeeks. You can the use start and end dates entered into
form controls to filter the calendar.

 

[Guest]

Yes, I did mean Main Report. Sorry!

I may have misunderstood the structure needed for tblWeekOf. The "Date"
field contains all dates for the period of the table, with "WeekOf" being the
appropriate first day of week for each "Date". qryReports.Date is the date of
the Show and not the same as 'WeekOf'. I had assumed I would need to join on
the 'Date' fields, which would give me the 'WeekOf' information I needed.

I'll have a go as suggested below.

Many thanks.

Don't you mean "main report" rather than "main form"? Where does
"tblWeekOf.Date" come from? I thought the only field in tblWeekOf would be
[WeekOf]. Is qryReports.Date the same as WeekOf?

I would try something like the following for the record source of the main
report.

SELECT WeekOf, IDShow, ShowTitle
FROM tblWeekOf, tblShows
WHERE WeekOf Between Forms!frmRptDates!txtStartDate and
Forms!frmRptDates!txtEndDate;

This assumes you have a table where shows are unique. This would send all
weeks and shows to your report within a particular date range.
--
Duane Hookom
Microsoft Access MVP

Thanks Duane,

I've done as suggested and created a tblWeekOf for several years into the
future! However I still can't get the calendars subforms to show 'empty'
records in weeks where there are no records. The query that supplies the data
for the main form looks like this now:

SELECT tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
FROM qryReports RIGHT JOIN tblWeekOf ON qryReports.Date = tblWeekOf.Date
GROUP BY tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
HAVING (((tblWeekOf.WeekOf) Between [Type the beginning date:] And [Type the
ending date:]));

Instead of Doctors I'm working with Shows and have temporarily used
parameters within the query for ease. This query seems to correctly throw up
all records within the parameter dates given (ie with WeekOf + IDShow + Show
Title) plus 'empty' records with just WeekOf data where there are no shows in
that week. qryReports provides the specific IDShow information.

The subreports are supplied by a query with all events and linked to the
main report by Child fields: Date;IDShow to Master fields: txtDay1;IDShow.

Its so nearly there, but can't quite get it to work! Anymore hints please?

Many thanks

The easiest method is to create a table with all weeks.
tblWeeks
WeekOf date/time

It may be easiest for you to create the weekof values in Excel and then
paste them into tblWeeks. You can the use start and end dates entered into
form controls to filter the calendar.

--
Duane Hookom
Microsoft Access MVP


:

I have adapted Duane Hookum's great monthly calendar report available at
http://www.access.hookom.net/Samples.htm. The only changes are to the date
formats and adding in weekend days. As expected, the report does not show
weeks when there are no records - it jumps to the start of the next week
where there are records.

However: (1) I would like to show weeks for which there are currently no
records so that notes can be added manually to hard copies before being
transferred to the database. (2) As a natural extension to the above, I need
to be able to select a date range for the report, which may preceed the
earliest or follow the latest weeks for which there are records.

Any suggestions from people who'ved used/adapted this report (Duane?!) or
similar reports would be greatfully received!

Many thanks

 

[Guest]

The main report should have only one date value per week which is the start
date of the week as you want in your report.

--
Duane Hookom
Microsoft Access MVP

Yes, I did mean Main Report. Sorry!

I may have misunderstood the structure needed for tblWeekOf. The "Date"
field contains all dates for the period of the table, with "WeekOf" being the
appropriate first day of week for each "Date". qryReports.Date is the date of
the Show and not the same as 'WeekOf'. I had assumed I would need to join on
the 'Date' fields, which would give me the 'WeekOf' information I needed.

I'll have a go as suggested below.

Many thanks.

Don't you mean "main report" rather than "main form"? Where does
"tblWeekOf.Date" come from? I thought the only field in tblWeekOf would be
[WeekOf]. Is qryReports.Date the same as WeekOf?

I would try something like the following for the record source of the main
report.

SELECT WeekOf, IDShow, ShowTitle
FROM tblWeekOf, tblShows
WHERE WeekOf Between Forms!frmRptDates!txtStartDate and
Forms!frmRptDates!txtEndDate;

This assumes you have a table where shows are unique. This would send all
weeks and shows to your report within a particular date range.
--
Duane Hookom
Microsoft Access MVP

Thanks Duane,

I've done as suggested and created a tblWeekOf for several years into the
future! However I still can't get the calendars subforms to show 'empty'
records in weeks where there are no records. The query that supplies the data
for the main form looks like this now:

SELECT tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
FROM qryReports RIGHT JOIN tblWeekOf ON qryReports.Date = tblWeekOf.Date
GROUP BY tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
HAVING (((tblWeekOf.WeekOf) Between [Type the beginning date:] And [Type the
ending date:]));

Instead of Doctors I'm working with Shows and have temporarily used
parameters within the query for ease. This query seems to correctly throw up
all records within the parameter dates given (ie with WeekOf + IDShow + Show
Title) plus 'empty' records with just WeekOf data where there are no shows in
that week. qryReports provides the specific IDShow information.

The subreports are supplied by a query with all events and linked to the
main report by Child fields: Date;IDShow to Master fields: txtDay1;IDShow.

Its so nearly there, but can't quite get it to work! Anymore hints please?

Many thanks

:

The easiest method is to create a table with all weeks.
tblWeeks
WeekOf date/time

It may be easiest for you to create the weekof values in Excel and then
paste them into tblWeeks. You can the use start and end dates entered into
form controls to filter the calendar.

--
Duane Hookom
Microsoft Access MVP


:

I have adapted Duane Hookum's great monthly calendar report available at
http://www.access.hookom.net/Samples.htm. The only changes are to the date
formats and adding in weekend days. As expected, the report does not show
weeks when there are no records - it jumps to the start of the next week
where there are records.

However: (1) I would like to show weeks for which there are currently no
records so that notes can be added manually to hard copies before being
transferred to the database. (2) As a natural extension to the above, I need
to be able to select a date range for the report, which may preceed the
earliest or follow the latest weeks for which there are records.

Any suggestions from people who'ved used/adapted this report (Duane?!) or
similar reports would be greatfully received!

Many thanks

 

[Guest]

Dear Duane,

Many thanks. Got it all to work perfectly after first incorporating your
suggestions into your original Doctors schedule example and then rebuilding
mine from scratch!

Very happy!

Best wishes

John

The main report should have only one date value per week which is the start
date of the week as you want in your report.

--
Duane Hookom
Microsoft Access MVP

Yes, I did mean Main Report. Sorry!

I may have misunderstood the structure needed for tblWeekOf. The "Date"
field contains all dates for the period of the table, with "WeekOf" being the
appropriate first day of week for each "Date". qryReports.Date is the date of
the Show and not the same as 'WeekOf'. I had assumed I would need to join on
the 'Date' fields, which would give me the 'WeekOf' information I needed.

I'll have a go as suggested below.

Many thanks.

Don't you mean "main report" rather than "main form"? Where does
"tblWeekOf.Date" come from? I thought the only field in tblWeekOf would be
[WeekOf]. Is qryReports.Date the same as WeekOf?

I would try something like the following for the record source of the main
report.

SELECT WeekOf, IDShow, ShowTitle
FROM tblWeekOf, tblShows
WHERE WeekOf Between Forms!frmRptDates!txtStartDate and
Forms!frmRptDates!txtEndDate;

This assumes you have a table where shows are unique. This would send all
weeks and shows to your report within a particular date range.
--
Duane Hookom
Microsoft Access MVP


:

Thanks Duane,

I've done as suggested and created a tblWeekOf for several years into the
future! However I still can't get the calendars subforms to show 'empty'
records in weeks where there are no records. The query that supplies the data
for the main form looks like this now:

SELECT tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
FROM qryReports RIGHT JOIN tblWeekOf ON qryReports.Date = tblWeekOf.Date
GROUP BY tblWeekOf.WeekOf, qryReports.IDShow, qryReports.ShowTitle
HAVING (((tblWeekOf.WeekOf) Between [Type the beginning date:] And [Type the
ending date:]));

Instead of Doctors I'm working with Shows and have temporarily used
parameters within the query for ease. This query seems to correctly throw up
all records within the parameter dates given (ie with WeekOf + IDShow + Show
Title) plus 'empty' records with just WeekOf data where there are no shows in
that week. qryReports provides the specific IDShow information.

The subreports are supplied by a query with all events and linked to the
main report by Child fields: Date;IDShow to Master fields: txtDay1;IDShow.

Its so nearly there, but can't quite get it to work! Anymore hints please?

Many thanks

:

The easiest method is to create a table with all weeks.
tblWeeks
WeekOf date/time

It may be easiest for you to create the weekof values in Excel and then
paste them into tblWeeks. You can the use start and end dates entered into
form controls to filter the calendar.

--
Duane Hookom
Microsoft Access MVP


:

I have adapted Duane Hookum's great monthly calendar report available at
http://www.access.hookom.net/Samples.htm. The only changes are to the date
formats and adding in weekend days. As expected, the report does not show
weeks when there are no records - it jumps to the start of the next week
where there are records.

However: (1) I would like to show weeks for which there are currently no
records so that notes can be added manually to hard copies before being
transferred to the database. (2) As a natural extension to the above, I need
to be able to select a date range for the report, which may preceed the
earliest or follow the latest weeks for which there are records.

Any suggestions from people who'ved used/adapted this report (Duane?!) or
similar reports would be greatfully received!

Many thanks