Printing sections of a report that only have data

  • Thread starter Kath via AccessMonster.com
  • Start date
K

Kath via AccessMonster.com

We are setting up a database for change flow management. This system will
have sections for each functional area (ie: Technology, Administrative
Support, Communications, etc.). This system will have a report for this "ID"
(Interface Document). What we are trying to achieve is having only the
functional areas with data populating the fields show on the report. How is
this best achieved? (I would like the section of the report not to show at
all if there is no data.)
 
M

Marshall Barton

Kath said:
We are setting up a database for change flow management. This system will
have sections for each functional area (ie: Technology, Administrative
Support, Communications, etc.). This system will have a report for this "ID"
(Interface Document). What we are trying to achieve is having only the
functional areas with data populating the fields show on the report. How is
this best achieved? (I would like the section of the report not to show at
all if there is no data.)
Click to expand...


The key to displaying the proper data in a report is to
create a query that returns only the needed data.

Please explain the tables involved and post a copy of the
report's record source query's SQL statement.
 
K

Kath via AccessMonster.com

Well...I did not design this db (one of my 'end users' did) and well.....
classic "I know how to create a database" design (everything is in one table.)
The Interface Document table basically holds all information on a "new or
change request" for the different functional areas within our organization.
So I would the record to be output on the report, but the secionts where it
states: "Areas Covered" is where it begins the sections where I would want to
exclude to print on the report. (I will probably look at the design if I
have time and make recommendations for this person to change some things.)

Here is the SQL from the query.
SELECT [FHCCP Interface Document].[ID #], [FHCCP Interface Document].[Time
Sensitive], [FHCCP Interface Document].[Open ID], [FHCCP Interface Document].
Type, [FHCCP Interface Document].Title, [FHCCP Interface Document].
[Contract/Program/Initiative], [FHCCP Interface Document].[Prepared By],
[FHCCP Interface Document].[Contract Period], [FHCCP Interface Document].
[Start Date], [FHCCP Interface Document].[End Date], [FHCCP Interface
Document].[POC(s)], [FHCCP Interface Document].[Copies To], [FHCCP Interface
Document].Affecting, [FHCCP Interface Document].[Division(s)], [FHCCP
Interface Document].CS, [FHCCP Interface Document].EX, [FHCCP Interface
Document].FA, [FHCCP Interface Document].NS, [FHCCP Interface Document].PD,
[FHCCP Interface Document].[Service Site(s)], [FHCCP Interface Document].
Others, [FHCCP Interface Document].[Actual Handoff Date], [FHCCP Interface
Document].[Expected Complition Date], [FHCCP Interface Document].[Actual
Completion Date], [FHCCP Interface Document].Communication, [FHCCP Interface
Document].[Dialog: Orig, & POC], [FHCCP Interface Document].[Dialog: POC &
Imp'r], [FHCCP Interface Document].[Dialog: Imp'r & Orig], [FHCCP Interface
Document].[Dialog: Other], [FHCCP Interface Document].Overview, [FHCCP
Interface Document].[This is a:], [FHCCP Interface Document].[Overview
Narrative], [FHCCP Interface Document].[Time Line:], [FHCCP Interface
Document].[Item 1], [FHCCP Interface Document].[Date 1], [FHCCP Interface
Document].[Item 2], [FHCCP Interface Document].[Date 2], [FHCCP Interface
Document].[Item 3], [FHCCP Interface Document].[Date 3], [FHCCP Interface
Document].[Item 4], [FHCCP Interface Document].[Date 4], [FHCCP Interface
Document].[Item 5], [FHCCP Interface Document].[Date 5], [FHCCP Interface
Document].[Item 6], [FHCCP Interface Document].[Date 6], [FHCCP Interface
Document].[Areas Covered], [FHCCP Interface Document].[Client Services],
[FHCCP Interface Document].Technology, [FHCCP Interface Document].[Site
Management], [FHCCP Interface Document].Staffing, [FHCCP Interface Document].
Fiscal, [FHCCP Interface Document].[Administrative Support], [FHCCP Interface
Document].Documentation, [FHCCP Interface Document].Training, [FHCCP
Interface Document].Reporting, [FHCCP Interface Document].Promotion, [FHCCP
Interface Document].Outcomes, [FHCCP Interface Document].[Evaluation Criteria]
, [FHCCP Interface Document].Monitoring, [FHCCP Interface Document].Other,
[FHCCP Interface Document].References, [FHCCP Interface Document].
[Attachments Included], [FHCCP Interface Document].[Budgetary Information],
[FHCCP Interface Document].[Table of Reports], [FHCCP Interface Document].
[Contract Workplan], [FHCCP Interface Document].[Contract/LOA Copy], [FHCCP
Interface Document].[Directive Document Copy], [FHCCP Interface Document].[IT
Needs Request], [FHCCP Interface Document].[Support Work Request], [FHCCP
Interface Document].[FYI Document], [FHCCP Interface Document].[Other
Attachment(s)]
FROM [FHCCP Interface Document];


Marshall said:
We are setting up a database for change flow management. This system will
have sections for each functional area (ie: Technology, Administrative
Click to expand...
[quoted text clipped - 3 lines]
this best achieved? (I would like the section of the report not to show at
all if there is no data.)
Click to expand...

The key to displaying the proper data in a report is to
create a query that returns only the needed data.

Please explain the tables involved and post a copy of the
report's record source query's SQL statement.
Click to expand...
 
M

Marshall Barton

Whoever created that table did not design a database. S/he
just plopped a spreadsheet into a table. Trying to do
anything more than just dumping all the data is going to be
very difficult. I have very little experience dealing with
something so terribly unnormalized and anything I suggest,
beyond a complete redesign of your data into several
relational tables, is going to be just guess work.

I couldn't find any field(s?) for "Areas Covered", so I
can't tell what the problem is from looking at the query.
Maybe using a subreport for the areas covered would allow
you to create a query for each "area" that can filter the
data the way you need. What a pain!

Actually, I got a headache after looking at that query for a
couple of minutes. How about at least setting the table's
Alias property to something short like "FID" just to make
the query's SQL a little easier to read.

The only other advice I can think of that might be
applicable is maybe you can create a UNION query that could
present the data in a more normalized structure to the
report or the report's record source query.
--
Marsh
MVP [MS Access]


Well...I did not design this db (one of my 'end users' did) and well.....
classic "I know how to create a database" design (everything is in one table.)
The Interface Document table basically holds all information on a "new or
change request" for the different functional areas within our organization.
So I would the record to be output on the report, but the secionts where it
states: "Areas Covered" is where it begins the sections where I would want to
exclude to print on the report. (I will probably look at the design if I
have time and make recommendations for this person to change some things.)

Here is the SQL from the query.
SELECT [FHCCP Interface Document].[ID #], [FHCCP Interface Document].[Time
Sensitive], [FHCCP Interface Document].[Open ID], [FHCCP Interface Document].
Type, [FHCCP Interface Document].Title, [FHCCP Interface Document].
[Contract/Program/Initiative], [FHCCP Interface Document].[Prepared By],
[FHCCP Interface Document].[Contract Period], [FHCCP Interface Document].
[Start Date], [FHCCP Interface Document].[End Date], [FHCCP Interface
Document].[POC(s)], [FHCCP Interface Document].[Copies To], [FHCCP Interface
Document].Affecting, [FHCCP Interface Document].[Division(s)], [FHCCP
Interface Document].CS, [FHCCP Interface Document].EX, [FHCCP Interface
Document].FA, [FHCCP Interface Document].NS, [FHCCP Interface Document].PD,
[FHCCP Interface Document].[Service Site(s)], [FHCCP Interface Document].
Others, [FHCCP Interface Document].[Actual Handoff Date], [FHCCP Interface
Document].[Expected Complition Date], [FHCCP Interface Document].[Actual
Completion Date], [FHCCP Interface Document].Communication, [FHCCP Interface
Document].[Dialog: Orig, & POC], [FHCCP Interface Document].[Dialog: POC &
Imp'r], [FHCCP Interface Document].[Dialog: Imp'r & Orig], [FHCCP Interface
Document].[Dialog: Other], [FHCCP Interface Document].Overview, [FHCCP
Interface Document].[This is a:], [FHCCP Interface Document].[Overview
Narrative], [FHCCP Interface Document].[Time Line:], [FHCCP Interface
Document].[Item 1], [FHCCP Interface Document].[Date 1], [FHCCP Interface
Document].[Item 2], [FHCCP Interface Document].[Date 2], [FHCCP Interface
Document].[Item 3], [FHCCP Interface Document].[Date 3], [FHCCP Interface
Document].[Item 4], [FHCCP Interface Document].[Date 4], [FHCCP Interface
Document].[Item 5], [FHCCP Interface Document].[Date 5], [FHCCP Interface
Document].[Item 6], [FHCCP Interface Document].[Date 6], [FHCCP Interface
Document].[Areas Covered], [FHCCP Interface Document].[Client Services],
[FHCCP Interface Document].Technology, [FHCCP Interface Document].[Site
Management], [FHCCP Interface Document].Staffing, [FHCCP Interface Document].
Fiscal, [FHCCP Interface Document].[Administrative Support], [FHCCP Interface
Document].Documentation, [FHCCP Interface Document].Training, [FHCCP
Interface Document].Reporting, [FHCCP Interface Document].Promotion, [FHCCP
Interface Document].Outcomes, [FHCCP Interface Document].[Evaluation Criteria]
, [FHCCP Interface Document].Monitoring, [FHCCP Interface Document].Other,
[FHCCP Interface Document].References, [FHCCP Interface Document].
[Attachments Included], [FHCCP Interface Document].[Budgetary Information],
[FHCCP Interface Document].[Table of Reports], [FHCCP Interface Document].
[Contract Workplan], [FHCCP Interface Document].[Contract/LOA Copy], [FHCCP
Interface Document].[Directive Document Copy], [FHCCP Interface Document].[IT
Needs Request], [FHCCP Interface Document].[Support Work Request], [FHCCP
Interface Document].[FYI Document], [FHCCP Interface Document].[Other
Attachment(s)]
FROM [FHCCP Interface Document];


Marshall said:
We are setting up a database for change flow management. This system will
have sections for each functional area (ie: Technology, Administrative
Click to expand...
[quoted text clipped - 3 lines]
this best achieved? (I would like the section of the report not to show at
all if there is no data.)
Click to expand...

The key to displaying the proper data in a report is to
create a query that returns only the needed data.

Please explain the tables involved and post a copy of the
report's record source query's SQL statement.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel print area which only prints pages of worksheet which are active 0
Pulling data into a form and making it save 7
Best way to tackle skipping blank data in report? 4
Print Detail section only conditionally 1
Protect Document with Sections 8
report with 3 sections need a query??? 3
Reports, Sub-reports running sum and no data 2
add new section in the report in VBA 1

Top