Select pages from multipage report

[slundrigan via AccessMonster.com]

I have a 4 page report. They are follow up letters that go to certain
clients. Often only 3 of the 4 pages need to be printed because of null
values in certain fields. Is there a way to filter printing to only those
pages that have data and not print the blank "Null Value" pages.

Stan.

 

[Duane Hookom]

Is there a reason why you don't simply filter the record source to remove the
records that have "null values in certain fields"?

 

[slundrigan via AccessMonster.com]

Thanks for the reply.

Each record addresses the same data on 1-4 clients. Then i do loop closure
by sending out these follow reports. There are multiple instances when blank
reports are printed and I wish to eliminate these reports. I was looking to
see if I could have the reports where the client name is null not be printed.

Thanks,

Stan.

 

[Duane Hookom]

I can't seem to follow your structure, requirements, or much else. At this
hour, I can't even see how your sentences relate one to another to create
something that I can understand.

Maybe someone else has better perception than I do. If not, please try to be
more specific about your tables and requirements.

 

[slundrigan via AccessMonster.com]

Sorry,

It was a long day for me too. Hopefully I can be clearer.

I review medical records (MRs). I created a form that includes the MR number,
other demographic information, and a series of yes/no audit questions. The
form also includes 4 combo boxes linked to 4 separate fields in my data table
(MD1 - MD4). These combo boxes list the staff physician names (~40) and each
combo box references the same list of physicians. Along side the combo boxes
are 4 yes/no check boxes (MDSig1 - MDSig4). I check the boxes during my
audits to indicate the need to obtain physician signatures on the medical
records (check = yes, signature required). A separate form is used to log
receipt of completed follow up reports with check boxes (MD1 Comp - MD4 Comp).
Both forms refer to just one table. The table stores records based on the MR
number.

My query references this data table and the following statements "IIf ((Data.
[MD1]=-1 And Data. [MD1 Comp]=0), Data. [MD1],"") AS MDSig1" list which
physicians still need follow up letters sent. There are 4 of these
statements generating MDSig1 - MDSig4. Occasionally there is only one
physician per MR. Sometimes there are 4, but usually there are 2-3.

My report is 4 pages and generates the 4 follow up letters. The header
includes medical record data specific to each MR (i.e. MR Number, Patient
Name, Diagnosis, etc.). The footer includes a physician signature / comment
section. The Detail section is split into 4 pages with page breaks and has a
place for physician name generated by the query (i.e. MDSig1 on page 1,
MDSig2 on page 2, etc.). This indicates who to mail the report to.

With this set up I get multiple pages with no physician name because a null
character "" is returned by my iif statement. Those pages get trashed when
the reports are printed. Likewise reviewing the forms in print preview shows
those reports.

I am wondering if there is a way to limit the report to show / print only
those pages with physician names on them...and not the ones with the null "".
That may be page 1 and page 4 for one record, pages 1-4 for the next, etc...

I hope this makes sense. I don’t know where to go from here.

Thanks again...and sorry for the earlier ramblings...

Stan.

 

[Duane Hookom]

Your problem is that you have un-normalized tables. Having your 1-4 fields is
not the accepted method for handling situations like this. You should have a
table where each record relates to a single MR and a single MD. Something
like:
tblMDReviews
================
mdrMDRID autonumber primary key
mdrMR link to existing table
mdrMD link to primary key of MD table
mdrComment
mdrStatus
You only add required records and hence don't have to worry about pages
showing up that aren't needed.

If you can't change your table structure, use a union query like:
SELECT MR as mdrMR, MD1 as mdrMD
FROM tblYourTable
WHERE MDSig1 = True
UNION ALL
SELECT MR, MD2
FROM tblYourTable
WHERE MDSig2 = True
UNION ALL
SELECT MR, MD3
FROM tblYourTable
WHERE MDSig3 = True
UNION ALL
SELECT MR, MD4
FROM tblYourTable
WHERE MDSig4 = True;

Then base your report on a query of tblYourTable and the union query. You
could also consider creating a subreport based on the union query.

--
Duane Hookom
Microsoft Access MVP

Sorry,

It was a long day for me too. Hopefully I can be clearer.

I review medical records (MRs). I created a form that includes the MR number,
other demographic information, and a series of yes/no audit questions. The
form also includes 4 combo boxes linked to 4 separate fields in my data table
(MD1 - MD4). These combo boxes list the staff physician names (~40) and each
combo box references the same list of physicians. Along side the combo boxes
are 4 yes/no check boxes (MDSig1 - MDSig4). I check the boxes during my
audits to indicate the need to obtain physician signatures on the medical
records (check = yes, signature required). A separate form is used to log
receipt of completed follow up reports with check boxes (MD1 Comp - MD4 Comp).
Both forms refer to just one table. The table stores records based on the MR
number.

My query references this data table and the following statements "IIf ((Data.
[MD1]=-1 And Data. [MD1 Comp]=0), Data. [MD1],"") AS MDSig1" list which
physicians still need follow up letters sent. There are 4 of these
statements generating MDSig1 - MDSig4. Occasionally there is only one
physician per MR. Sometimes there are 4, but usually there are 2-3.

My report is 4 pages and generates the 4 follow up letters. The header
includes medical record data specific to each MR (i.e. MR Number, Patient
Name, Diagnosis, etc.). The footer includes a physician signature / comment
section. The Detail section is split into 4 pages with page breaks and has a
place for physician name generated by the query (i.e. MDSig1 on page 1,
MDSig2 on page 2, etc.). This indicates who to mail the report to.

With this set up I get multiple pages with no physician name because a null
character "" is returned by my iif statement. Those pages get trashed when
the reports are printed. Likewise reviewing the forms in print preview shows
those reports.

I am wondering if there is a way to limit the report to show / print only
those pages with physician names on them...and not the ones with the null "".
That may be page 1 and page 4 for one record, pages 1-4 for the next, etc...

I hope this makes sense. I don’t know where to go from here.

Thanks again...and sorry for the earlier ramblings...

Stan.

 

[slundrigan via AccessMonster.com]

Duane,

Thanks so much. The union query worked perfectly. Happy new year.

Stan.