Omit Select Data From Report

[Julian501]

Is there any way to delete certain data from my report and have access
reformat the report automatically by removing the unused rows from the detail
field?

 

[Jeff Boyce]

Reports in Access reflect their underlying source ... a query or a table.

When you say "unused rows ...[in] the detail [band/section]", what do you
mean? Usually, an Access report will only have rows in the detail section
that reflect the data from the underlying source.

Can you provide a more specific example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Julian501]

For instance, my query has 24 fields, and all 24 are listed in a column on t
he report with a horizontal line between each one. However, 1 or more of
those fields may be null values on a given report depending on the query
parameters. When I created the report, I inserted all 24 fields in the
report grid and drew a horizontal line between them. My report now shows all
24 fields whether or not they contain data.

Now I am trying to find out if there is a command that will tell Access to
look at the field and close up the space and maybe even delete the line if
the field contains a null value. The resulting report would only show those
items that had data and close up the space where the field was a null value.
In essence, it the report would be compressed or reformated as it printed
depending on whether there was data in the field.

Thanks.

Reports in Access reflect their underlying source ... a query or a table.

When you say "unused rows ...[in] the detail [band/section]", what do you
mean? Usually, an Access report will only have rows in the detail section
that reflect the data from the underlying source.

Can you provide a more specific example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there any way to delete certain data from my report and have access
reformat the report automatically by removing the unused rows from the
detail
field?

 

[Duane Hookom]

Depending on your table structure, you might get by with creating a large
union query so the 24 fields become 24 related records. If a field is null,
exclude it from the result of the union query. You could then display this
information with a subreport.

By any chance, are the 24 fields similar like representing hours in a day or
2 years of months or whatever?

--
Duane Hookom
MS Access MVP

For instance, my query has 24 fields, and all 24 are listed in a column on
t
he report with a horizontal line between each one. However, 1 or more of
those fields may be null values on a given report depending on the query
parameters. When I created the report, I inserted all 24 fields in the
report grid and drew a horizontal line between them. My report now shows
all
24 fields whether or not they contain data.

Now I am trying to find out if there is a command that will tell Access to
look at the field and close up the space and maybe even delete the line if
the field contains a null value. The resulting report would only show
those
items that had data and close up the space where the field was a null
value.
In essence, it the report would be compressed or reformated as it printed
depending on whether there was data in the field.

Thanks.

Reports in Access reflect their underlying source ... a query or a table.

When you say "unused rows ...[in] the detail [band/section]", what do you
mean? Usually, an Access report will only have rows in the detail
section
that reflect the data from the underlying source.

Can you provide a more specific example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there any way to delete certain data from my report and have access
reformat the report automatically by removing the unused rows from the
detail
field?

 

[Julian501]

The fields are not similar. My database is designed to track and measure the
time it takes to issue government contracts. There are several different
types of contracts with as many as 2 dozen steps required to issue each one.
The contracting officer determines which steps are required depending on the
situation. I could not find anything in the help menu or Microsoft's Access
Inside Out, and I suspected that it would require VBA code which is way
beyond my ability level right now.

Thank you for the helpful suggestion. I will see if there is someway it can
be applied to my problem.

Depending on your table structure, you might get by with creating a large
union query so the 24 fields become 24 related records. If a field is null,
exclude it from the result of the union query. You could then display this
information with a subreport.

By any chance, are the 24 fields similar like representing hours in a day or
2 years of months or whatever?

--
Duane Hookom
MS Access MVP

For instance, my query has 24 fields, and all 24 are listed in a column on
t
he report with a horizontal line between each one. However, 1 or more of
those fields may be null values on a given report depending on the query
parameters. When I created the report, I inserted all 24 fields in the
report grid and drew a horizontal line between them. My report now shows
all
24 fields whether or not they contain data.

Now I am trying to find out if there is a command that will tell Access to
look at the field and close up the space and maybe even delete the line if
the field contains a null value. The resulting report would only show
those
items that had data and close up the space where the field was a null
value.
In essence, it the report would be compressed or reformated as it printed
depending on whether there was data in the field.

Thanks.

Reports in Access reflect their underlying source ... a query or a table.

When you say "unused rows ...[in] the detail [band/section]", what do you
mean? Usually, an Access report will only have rows in the detail
section
that reflect the data from the underlying source.

Can you provide a more specific example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there any way to delete certain data from my report and have access
reformat the report automatically by removing the unused rows from the
detail
field?

 

[Duane Hookom]

Your issue seems what I expected. Are these 2 dozen date fields that
represent steps? To me, that is "similar". IMHO you are in the word of fellow
MVP Jeff Boyce "committing spreadsheet". I would normalize this table
structure so that each date creates its own record in a related table. For
instance:

tblContracts
==============
ContractID primary key
ContractTitle

tblSteps (as many as 2 dozen records)
=============
StepID primary key
StepTitle

tblContractSteps
================
ContStepID
ContractID links to tblContracts.ContractID
StepID links to tblSteps.StepID
StepDate
StepComments

This would allow you add or remove steps as required. The results could be
reported in a subreport where you would not need any code.
--
Duane Hookom
Microsoft Access MVP

The fields are not similar. My database is designed to track and measure the
time it takes to issue government contracts. There are several different
types of contracts with as many as 2 dozen steps required to issue each one.
The contracting officer determines which steps are required depending on the
situation. I could not find anything in the help menu or Microsoft's Access
Inside Out, and I suspected that it would require VBA code which is way
beyond my ability level right now.

Thank you for the helpful suggestion. I will see if there is someway it can
be applied to my problem.

Depending on your table structure, you might get by with creating a large
union query so the 24 fields become 24 related records. If a field is null,
exclude it from the result of the union query. You could then display this
information with a subreport.

By any chance, are the 24 fields similar like representing hours in a day or
2 years of months or whatever?

--
Duane Hookom
MS Access MVP

For instance, my query has 24 fields, and all 24 are listed in a column on
t
he report with a horizontal line between each one. However, 1 or more of
those fields may be null values on a given report depending on the query
parameters. When I created the report, I inserted all 24 fields in the
report grid and drew a horizontal line between them. My report now shows
all
24 fields whether or not they contain data.

Now I am trying to find out if there is a command that will tell Access to
look at the field and close up the space and maybe even delete the line if
the field contains a null value. The resulting report would only show
those
items that had data and close up the space where the field was a null
value.
In essence, it the report would be compressed or reformated as it printed
depending on whether there was data in the field.

Thanks.

:

Reports in Access reflect their underlying source ... a query or a table.

When you say "unused rows ...[in] the detail [band/section]", what do you
mean? Usually, an Access report will only have rows in the detail
section
that reflect the data from the underlying source.

Can you provide a more specific example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there any way to delete certain data from my report and have access
reformat the report automatically by removing the unused rows from the
detail
field?

 

[Julian501]

You are correct in that I am committing spreadsheet. Most of my work is done
in Excel and that affected how I visualized the solution. Your suggestion is
very insightful and has me rethinking my tables and how to normalize them. I
was gun-shy about establishing multiple tables and relating the data. I did
not run the analyzer yet.

You keep referring to subreports. What is their advantage over a normal
report?

Your issue seems what I expected. Are these 2 dozen date fields that
represent steps? To me, that is "similar". IMHO you are in the word of fellow
MVP Jeff Boyce "committing spreadsheet". I would normalize this table
structure so that each date creates its own record in a related table. For
instance:

tblContracts
==============
ContractID primary key
ContractTitle

tblSteps (as many as 2 dozen records)
=============
StepID primary key
StepTitle

tblContractSteps
================
ContStepID
ContractID links to tblContracts.ContractID
StepID links to tblSteps.StepID
StepDate
StepComments

This would allow you add or remove steps as required. The results could be
reported in a subreport where you would not need any code.
--
Duane Hookom
Microsoft Access MVP

The fields are not similar. My database is designed to track and measure the
time it takes to issue government contracts. There are several different
types of contracts with as many as 2 dozen steps required to issue each one.
The contracting officer determines which steps are required depending on the
situation. I could not find anything in the help menu or Microsoft's Access
Inside Out, and I suspected that it would require VBA code which is way
beyond my ability level right now.

Thank you for the helpful suggestion. I will see if there is someway it can
be applied to my problem.

Depending on your table structure, you might get by with creating a large
union query so the 24 fields become 24 related records. If a field is null,
exclude it from the result of the union query. You could then display this
information with a subreport.

By any chance, are the 24 fields similar like representing hours in a day or
2 years of months or whatever?

--
Duane Hookom
MS Access MVP

For instance, my query has 24 fields, and all 24 are listed in a column on
t
he report with a horizontal line between each one. However, 1 or more of
those fields may be null values on a given report depending on the query
parameters. When I created the report, I inserted all 24 fields in the
report grid and drew a horizontal line between them. My report now shows
all
24 fields whether or not they contain data.

Now I am trying to find out if there is a command that will tell Access to
look at the field and close up the space and maybe even delete the line if
the field contains a null value. The resulting report would only show
those
items that had data and close up the space where the field was a null
value.
In essence, it the report would be compressed or reformated as it printed
depending on whether there was data in the field.

Thanks.

:

Reports in Access reflect their underlying source ... a query or a table.

When you say "unused rows ...[in] the detail [band/section]", what do you
mean? Usually, an Access report will only have rows in the detail
section
that reflect the data from the underlying source.

Can you provide a more specific example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there any way to delete certain data from my report and have access
reformat the report automatically by removing the unused rows from the
detail
field?

 

[Duane Hookom]

Your first question suggested removing horizontal lines which means your
fields were displayed vertically. If however the fields displayed
horizontally, you could create a main report based on contracts and a
multiple column subreport based on the contract steps.

If your steps are to be displayed vertically, a subreport would not be
necessary.
--
Duane Hookom
Microsoft Access MVP

You are correct in that I am committing spreadsheet. Most of my work is done
in Excel and that affected how I visualized the solution. Your suggestion is
very insightful and has me rethinking my tables and how to normalize them. I
was gun-shy about establishing multiple tables and relating the data. I did
not run the analyzer yet.

You keep referring to subreports. What is their advantage over a normal
report?

Your issue seems what I expected. Are these 2 dozen date fields that
represent steps? To me, that is "similar". IMHO you are in the word of fellow
MVP Jeff Boyce "committing spreadsheet". I would normalize this table
structure so that each date creates its own record in a related table. For
instance:

tblContracts
==============
ContractID primary key
ContractTitle

tblSteps (as many as 2 dozen records)
=============
StepID primary key
StepTitle

tblContractSteps
================
ContStepID
ContractID links to tblContracts.ContractID
StepID links to tblSteps.StepID
StepDate
StepComments

This would allow you add or remove steps as required. The results could be
reported in a subreport where you would not need any code.
--
Duane Hookom
Microsoft Access MVP

The fields are not similar. My database is designed to track and measure the
time it takes to issue government contracts. There are several different
types of contracts with as many as 2 dozen steps required to issue each one.
The contracting officer determines which steps are required depending on the
situation. I could not find anything in the help menu or Microsoft's Access
Inside Out, and I suspected that it would require VBA code which is way
beyond my ability level right now.

Thank you for the helpful suggestion. I will see if there is someway it can
be applied to my problem.

:

Depending on your table structure, you might get by with creating a large
union query so the 24 fields become 24 related records. If a field is null,
exclude it from the result of the union query. You could then display this
information with a subreport.

By any chance, are the 24 fields similar like representing hours in a day or
2 years of months or whatever?

--
Duane Hookom
MS Access MVP

For instance, my query has 24 fields, and all 24 are listed in a column on
t
he report with a horizontal line between each one. However, 1 or more of
those fields may be null values on a given report depending on the query
parameters. When I created the report, I inserted all 24 fields in the
report grid and drew a horizontal line between them. My report now shows
all
24 fields whether or not they contain data.

Now I am trying to find out if there is a command that will tell Access to
look at the field and close up the space and maybe even delete the line if
the field contains a null value. The resulting report would only show
those
items that had data and close up the space where the field was a null
value.
In essence, it the report would be compressed or reformated as it printed
depending on whether there was data in the field.

Thanks.

:

Reports in Access reflect their underlying source ... a query or a table.

When you say "unused rows ...[in] the detail [band/section]", what do you
mean? Usually, an Access report will only have rows in the detail
section
that reflect the data from the underlying source.

Can you provide a more specific example?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Is there any way to delete certain data from my report and have access
reformat the report automatically by removing the unused rows from the
detail
field?