Access Report Printing and filtering

[Dina]

I have a productivity report that is based on the information I enter into a
form. The report contains information on a staff of five with each staff
having a total at the end of the month in four catagories. The data is
entered by date.

There's a few things I would like for the report to do. I would like to
total the catagories at the end of each month for each staff. I've completed
the month of May and I'm entering the month of June but the calulation is
including the totals from May as well. In other words, June's data is being
added to May's data.
I would like to be able to filter the report by month and print it out with
only the totals for that month. I would also like to have a command button
on the form to print the report. Can anyone give me step by step
instructions? I'm a new user

 

[Ken Sheridan]

You can group the report by month and then by employee, and then by category.
Give the employee and month groups group headers, and the category group a
group footer. Put the employee name in the employee header, the month in the
month header and a text box to total the relevant field in the category group
footer. This will five you totals for each employee for each month.

First you'll need a column in the report's underlying recordset to return
the month for the date value in each row. To do this base the report on a
query which returns all the necessary fields from the relevant table(s), and
in query design row out the following in t 'field' row of a blank column:

WorkYearMonth: Format([YourDateField],"yyyymm")

where YourDateField is the name of the field containing the dates. This
will return the month in the format 2000807 (for this month). Its important
that the year is included as otherwise data from the same months in different
years will all be grouped together.

For the month group in the report group on the WorkYearMonth column. To
show the month in a more meaningful format in the group herder add a text box
with a ControlSource property of:

=Format([YourdateField], "mmmm yyyy")

This will show it as July 2008.

To filter the report to a particular month add two more computed columns to
the underlying query by putting the following in the 'field' row of two more
columns in query design view:

WorkYear: Year([YourDateField])

WorkMonth: Month([YourDateField])

These columns won't appear in the report itself, they are purely for
restricting the results to one year/month. To do this enter the following in
the first 'criteria' row of the WorkYear column in query design view:

[Enter year:] Or [Enter Year:] Is Null

and this in the WorkMonth column's first 'criteria' row:

[Enter month as a number 1-12:] Or [Enter month as a number 1-12:] Is Null

You'll find that if you save the query and then open it again in design view
Access will have moved things around. Don't worry, it will work just the
same,

When you open the report you'll be prompted first to enter the year and then
to enter the month. The query's result set, and hence the report will then
be restricted to the year/month entered. By testing each for OR IS NULL this
makes them optional, so if you enter a year but just press Enter at the month
prompt without entering a month the report will be filtered to the whole
year. If you enter neither a year nor a month the report will show all
records unfiltered. In either case the totals will be grouped per
month/employee in the report.

To add a command button to a form for printing the report you can use the
button wizard, but the code for such a button's Click event procedure is in
essence very simple:

DoCmd.OpenReport "YourReportNameGoesHere"

If you also want a button to preview the report its:

DoCmd.OpenReport "YourReportNameGoesHere", View:=acViewPreview

Ken Sheridan
Stafford, England

 

[Dina]

When you say base my report on a query, do you mean I have to recreate the
report I already have designed?

You can group the report by month and then by employee, and then by category.
Give the employee and month groups group headers, and the category group a
group footer. Put the employee name in the employee header, the month in the
month header and a text box to total the relevant field in the category group
footer. This will five you totals for each employee for each month.

First you'll need a column in the report's underlying recordset to return
the month for the date value in each row. To do this base the report on a
query which returns all the necessary fields from the relevant table(s), and
in query design row out the following in t 'field' row of a blank column:

WorkYearMonth: Format([YourDateField],"yyyymm")

where YourDateField is the name of the field containing the dates. This
will return the month in the format 2000807 (for this month). Its important
that the year is included as otherwise data from the same months in different
years will all be grouped together.

For the month group in the report group on the WorkYearMonth column. To
show the month in a more meaningful format in the group herder add a text box
with a ControlSource property of:

=Format([YourdateField], "mmmm yyyy")

This will show it as July 2008.

To filter the report to a particular month add two more computed columns to
the underlying query by putting the following in the 'field' row of two more
columns in query design view:

WorkYear: Year([YourDateField])

WorkMonth: Month([YourDateField])

These columns won't appear in the report itself, they are purely for
restricting the results to one year/month. To do this enter the following in
the first 'criteria' row of the WorkYear column in query design view:

[Enter year:] Or [Enter Year:] Is Null

and this in the WorkMonth column's first 'criteria' row:

[Enter month as a number 1-12:] Or [Enter month as a number 1-12:] Is Null

You'll find that if you save the query and then open it again in design view
Access will have moved things around. Don't worry, it will work just the
same,

When you open the report you'll be prompted first to enter the year and then
to enter the month. The query's result set, and hence the report will then
be restricted to the year/month entered. By testing each for OR IS NULL this
makes them optional, so if you enter a year but just press Enter at the month
prompt without entering a month the report will be filtered to the whole
year. If you enter neither a year nor a month the report will show all
records unfiltered. In either case the totals will be grouped per
month/employee in the report.

To add a command button to a form for printing the report you can use the
button wizard, but the code for such a button's Click event procedure is in
essence very simple:

DoCmd.OpenReport "YourReportNameGoesHere"

If you also want a button to preview the report its:

DoCmd.OpenReport "YourReportNameGoesHere", View:=acViewPreview

Ken Sheridan
Stafford, England

I have a productivity report that is based on the information I enter into a
form. The report contains information on a staff of five with each staff
having a total at the end of the month in four catagories. The data is
entered by date.

There's a few things I would like for the report to do. I would like to
total the catagories at the end of each month for each staff. I've completed
the month of May and I'm entering the month of June but the calulation is
including the totals from May as well. In other words, June's data is being
added to May's data.
I would like to be able to filter the report by month and print it out with
only the totals for that month. I would also like to have a command button
on the form to print the report. Can anyone give me step by step
instructions? I'm a new user

 

[Ken Sheridan]

Not necessarily. Provided the query returns all the necessary columns
you can change the report's RecordSource property to the name of the query.
If its not grouped in the way I described then you can amend its design
accordingly.

Ken Sheridan
Stafford, England

When you say base my report on a query, do you mean I have to recreate the
report I already have designed?

You can group the report by month and then by employee, and then by category.
Give the employee and month groups group headers, and the category group a
group footer. Put the employee name in the employee header, the month in the
month header and a text box to total the relevant field in the category group
footer. This will five you totals for each employee for each month.

First you'll need a column in the report's underlying recordset to return
the month for the date value in each row. To do this base the report on a
query which returns all the necessary fields from the relevant table(s), and
in query design row out the following in t 'field' row of a blank column:

WorkYearMonth: Format([YourDateField],"yyyymm")

where YourDateField is the name of the field containing the dates. This
will return the month in the format 2000807 (for this month). Its important
that the year is included as otherwise data from the same months in different
years will all be grouped together.

For the month group in the report group on the WorkYearMonth column. To
show the month in a more meaningful format in the group herder add a text box
with a ControlSource property of:

=Format([YourdateField], "mmmm yyyy")

This will show it as July 2008.

To filter the report to a particular month add two more computed columns to
the underlying query by putting the following in the 'field' row of two more
columns in query design view:

WorkYear: Year([YourDateField])

WorkMonth: Month([YourDateField])

These columns won't appear in the report itself, they are purely for
restricting the results to one year/month. To do this enter the following in
the first 'criteria' row of the WorkYear column in query design view:

[Enter year:] Or [Enter Year:] Is Null

and this in the WorkMonth column's first 'criteria' row:

[Enter month as a number 1-12:] Or [Enter month as a number 1-12:] Is Null

You'll find that if you save the query and then open it again in design view
Access will have moved things around. Don't worry, it will work just the
same,

When you open the report you'll be prompted first to enter the year and then
to enter the month. The query's result set, and hence the report will then
be restricted to the year/month entered. By testing each for OR IS NULL this
makes them optional, so if you enter a year but just press Enter at the month
prompt without entering a month the report will be filtered to the whole
year. If you enter neither a year nor a month the report will show all
records unfiltered. In either case the totals will be grouped per
month/employee in the report.

To add a command button to a form for printing the report you can use the
button wizard, but the code for such a button's Click event procedure is in
essence very simple:

DoCmd.OpenReport "YourReportNameGoesHere"

If you also want a button to preview the report its:

DoCmd.OpenReport "YourReportNameGoesHere", View:=acViewPreview

Ken Sheridan
Stafford, England

I have a productivity report that is based on the information I enter into a
form. The report contains information on a staff of five with each staff
having a total at the end of the month in four catagories. The data is
entered by date.

There's a few things I would like for the report to do. I would like to
total the catagories at the end of each month for each staff. I've completed
the month of May and I'm entering the month of June but the calulation is
including the totals from May as well. In other words, June's data is being
added to May's data.
I would like to be able to filter the report by month and print it out with
only the totals for that month. I would also like to have a command button
on the form to print the report. Can anyone give me step by step
instructions? I'm a new user

 

[Dina]

Thanks so very much,
This works like a charm. I would also like to display the grand total of
the four productivity catagories for each employee in the report. I am able
to enter the code in the report to get a total but when I try to enter the
code in the report to get the grand total, the field will not calculate.
The code I'm using is:
Sum([My field name])+Sum([My field name])+Sum([My field name])+Sum([My field
name])

Not necessarily. Provided the query returns all the necessary columns
you can change the report's RecordSource property to the name of the query.
If its not grouped in the way I described then you can amend its design
accordingly.

Ken Sheridan
Stafford, England

When you say base my report on a query, do you mean I have to recreate the
report I already have designed?

You can group the report by month and then by employee, and then by category.
Give the employee and month groups group headers, and the category group a
group footer. Put the employee name in the employee header, the month in the
month header and a text box to total the relevant field in the category group
footer. This will five you totals for each employee for each month.

First you'll need a column in the report's underlying recordset to return
the month for the date value in each row. To do this base the report on a
query which returns all the necessary fields from the relevant table(s), and
in query design row out the following in t 'field' row of a blank column:

WorkYearMonth: Format([YourDateField],"yyyymm")

where YourDateField is the name of the field containing the dates. This
will return the month in the format 2000807 (for this month). Its important
that the year is included as otherwise data from the same months in different
years will all be grouped together.

For the month group in the report group on the WorkYearMonth column. To
show the month in a more meaningful format in the group herder add a text box
with a ControlSource property of:

=Format([YourdateField], "mmmm yyyy")

This will show it as July 2008.

To filter the report to a particular month add two more computed columns to
the underlying query by putting the following in the 'field' row of two more
columns in query design view:

WorkYear: Year([YourDateField])

WorkMonth: Month([YourDateField])

These columns won't appear in the report itself, they are purely for
restricting the results to one year/month. To do this enter the following in
the first 'criteria' row of the WorkYear column in query design view:

[Enter year:] Or [Enter Year:] Is Null

and this in the WorkMonth column's first 'criteria' row:

[Enter month as a number 1-12:] Or [Enter month as a number 1-12:] Is Null

You'll find that if you save the query and then open it again in design view
Access will have moved things around. Don't worry, it will work just the
same,

When you open the report you'll be prompted first to enter the year and then
to enter the month. The query's result set, and hence the report will then
be restricted to the year/month entered. By testing each for OR IS NULL this
makes them optional, so if you enter a year but just press Enter at the month
prompt without entering a month the report will be filtered to the whole
year. If you enter neither a year nor a month the report will show all
records unfiltered. In either case the totals will be grouped per
month/employee in the report.

To add a command button to a form for printing the report you can use the
button wizard, but the code for such a button's Click event procedure is in
essence very simple:

DoCmd.OpenReport "YourReportNameGoesHere"

If you also want a button to preview the report its:

DoCmd.OpenReport "YourReportNameGoesHere", View:=acViewPreview

Ken Sheridan
Stafford, England

:

I have a productivity report that is based on the information I enter into a
form. The report contains information on a staff of five with each staff
having a total at the end of the month in four catagories. The data is
entered by date.

There's a few things I would like for the report to do. I would like to
total the catagories at the end of each month for each staff. I've completed
the month of May and I'm entering the month of June but the calulation is
including the totals from May as well. In other words, June's data is being
added to May's data.
I would like to be able to filter the report by month and print it out with
only the totals for that month. I would also like to have a command button
on the form to print the report. Can anyone give me step by step
instructions? I'm a new user

 

[Ken Sheridan]

The sum of all categories is the sum of [My field name] grouped by employee,
so all you need to do is include a group footer section for the employee
group and sum the value in that in exactly the same way as in the category
footer with:

=Sum(My field name])

While on the subject of grouping it might be worth looking at the subject of
grouping by people in a little more detail. People's names may be duplicated
of course, even in a small workgroup; I once worked with two Maggie Taylors.
So its important that each person is uniquely identified, e.g. by a numeric
EmployeeID, for which an autonumber can conveniently be used. Otherwise both
Maggie Taylors would be reported as one employee if the grouping is by name.

It would be possible to group a report by EmployeeID to ensure that each
employee is reported separately, even if they have the same names, but as
EmployeeID is an arbitrary value the order would then be on those arbitrary
values, whereas generally ordering alphabetically by LastName then FirstName
is preferred. The way to do this to first group the report by LastName, then
by FirstName the by EmployeeID but give only the EmployeeID group a group
header and/or footer and put the relevant employee data controls in that
footer or header. In this way the two Maggies would be correctly reported as
separate employees.

Ken Sheridan
Stafford, England

Thanks so very much,
This works like a charm. I would also like to display the grand total of
the four productivity catagories for each employee in the report. I am able
to enter the code in the report to get a total but when I try to enter the
code in the report to get the grand total, the field will not calculate.
The code I'm using is:
Sum([My field name])+Sum([My field name])+Sum([My field name])+Sum([My field
name])

Not necessarily. Provided the query returns all the necessary columns
you can change the report's RecordSource property to the name of the query.
If its not grouped in the way I described then you can amend its design
accordingly.

Ken Sheridan
Stafford, England

When you say base my report on a query, do you mean I have to recreate the
report I already have designed?

:

You can group the report by month and then by employee, and then by category.
Give the employee and month groups group headers, and the category group a
group footer. Put the employee name in the employee header, the month in the
month header and a text box to total the relevant field in the category group
footer. This will five you totals for each employee for each month.

First you'll need a column in the report's underlying recordset to return
the month for the date value in each row. To do this base the report on a
query which returns all the necessary fields from the relevant table(s), and
in query design row out the following in t 'field' row of a blank column:

WorkYearMonth: Format([YourDateField],"yyyymm")

where YourDateField is the name of the field containing the dates. This
will return the month in the format 2000807 (for this month). Its important
that the year is included as otherwise data from the same months in different
years will all be grouped together.

For the month group in the report group on the WorkYearMonth column. To
show the month in a more meaningful format in the group herder add a text box
with a ControlSource property of:

=Format([YourdateField], "mmmm yyyy")

This will show it as July 2008.

To filter the report to a particular month add two more computed columns to
the underlying query by putting the following in the 'field' row of two more
columns in query design view:

WorkYear: Year([YourDateField])

WorkMonth: Month([YourDateField])

These columns won't appear in the report itself, they are purely for
restricting the results to one year/month. To do this enter the following in
the first 'criteria' row of the WorkYear column in query design view:

[Enter year:] Or [Enter Year:] Is Null

and this in the WorkMonth column's first 'criteria' row:

[Enter month as a number 1-12:] Or [Enter month as a number 1-12:] Is Null

You'll find that if you save the query and then open it again in design view
Access will have moved things around. Don't worry, it will work just the
same,

When you open the report you'll be prompted first to enter the year and then
to enter the month. The query's result set, and hence the report will then
be restricted to the year/month entered. By testing each for OR IS NULL this
makes them optional, so if you enter a year but just press Enter at the month
prompt without entering a month the report will be filtered to the whole
year. If you enter neither a year nor a month the report will show all
records unfiltered. In either case the totals will be grouped per
month/employee in the report.

To add a command button to a form for printing the report you can use the
button wizard, but the code for such a button's Click event procedure is in
essence very simple:

DoCmd.OpenReport "YourReportNameGoesHere"

If you also want a button to preview the report its:

DoCmd.OpenReport "YourReportNameGoesHere", View:=acViewPreview

Ken Sheridan
Stafford, England

:

I have a productivity report that is based on the information I enter into a
form. The report contains information on a staff of five with each staff
having a total at the end of the month in four catagories. The data is
entered by date.

There's a few things I would like for the report to do. I would like to
total the catagories at the end of each month for each staff. I've completed
the month of May and I'm entering the month of June but the calulation is
including the totals from May as well. In other words, June's data is being
added to May's data.
I would like to be able to filter the report by month and print it out with
only the totals for that month. I would also like to have a command button
on the form to print the report. Can anyone give me step by step
instructions? I'm a new user