Only print pages with data

M

Muneer Mikel

Hi,

I have a report based on a quary from two tables. Each record is printed on
a separate page. What I want is not to print the records with empty fields
(the selected fields from the query). So when I print the report, it only
prints the pages with data on.
I tried to use "Is Not Null" in the "OR" crieteria in the query for all the
selected fields, yet it is not giving me the right number of records.

Thank you
Muneer
 
R

Rick B

You are using 'field' and 'record' in this post in a way that makes it very
hard to understand.

You need to modify your query so that it only pulls the data you want on
your report. If your query is pulling more data than desired (i.e. pulling
blank records) then you would need to post your query here so we can see it
and determine what is wrong. You might also give us an example of a record
that is pulling up that should not.

A bigger question is why you have records with no data.

This could be the way you have your join set between the two tables. If you
are pulling all records from both tables, then you might be getting more
data than desired. You might look at the join and modify it to pull all
record from your main table and only those records in the secondary table
where the joined fields are equal. To do so, right-click on the line
connecting the two tables while in query design-view and modify the join
type.
 
E

Eric D via AccessMonster.com

It's very difficult to help when one does not know the type of data being
using, the query one is having problems with or no example of the expected
results.

I created two simple tables, linked them together via Tools/Relationships. I
then opened the primary table which contains RecID, Fname, Lname, Address,
City, State. The secondary table simply has RecID (foreign key) and one other
field. In the primary table, I entered 8 records. One record did not contain
an Address, one did not contain a City and one did not contain a State.

I created a query, using both tables - All fields from the primary table and
the one field from the secondary table. I set the Criteria field under
Address, City and State to "Not Is Null" and ran the query.

My results filtered those records that did not contain data in any of those
fields. My query returned 4 records. I added another record with two of the
noted fields empty (just to be sure). My results were the same. I believe
this is what you are trying to do. If so, you may have something wrong with
your query or your join. If not, please provide additional information.
 
M

Muneer Mikel

Rick, Eric, thank you for your reply.

In my query "qryMedical" I'm pulling data from these two tables:

tblMain

MemberID Number PK
FirstName Text
LastName Text
RegistrationID Number
Allergies Yes/No
Walker Yes/No
Wheelchair Yes/No


tblMoreMedicalInfo

MemberID Number
Operation Yes/No
Medication Yes/No
Diabetic Yes/No

The relationship between the two tables is One-to-One, and the join type is
option 2 (Include All records from "tblMain" and only those records from
"Medical"....)

I have a main form "frmMain" where I have controls linked to these tables
and also other tables. The form is devided into tabs, one of the tabs is
called "More Medical Info" that has all the fields from "tblMoreMedicalInfo".
When the user entres the data for a member into this form, he/she does not
enter any info in this tab if the member dosen't have extra info.
In the "qryMedical" I need to pull out only the records that have data in
either one of these(Allergies, Walker, Wheelchair from tblMain) or data in
(Operation, Medication, Diabetic form tblMoreMedcialInfo). In other words, I
don't want to see a record with empty data in these fields.
After reading your posts, I noticed in "qryMedical" that some of the empty
records, the check boxs for (Allergies, Walker, Wheelchair from tblMain) are
active , however, (Operation, Medication, Diabetic form tblMoreMedcialInfo)
are inactive. I don't know if this is a part of the problem!

Thank you
Muneer
 
E

Eric D via AccessMonster.com

Based on your information you last supplied, I would redo the two tables.
In my opinion, you've mixed medical info with patient info and should modify
the tables as follows:

tblMain:
MemberID (pKey)
FirstName
LastName
RegistrationID

tblMedical_Info
MemberID (fKey)
Allergies Yes/No
Walker Yes/No
Wheelchair Yes/No
Operation Yes/No
Medication Yes/No
Diabetic Yes/No

Redo your query, using the above and check to see if you get better results.
My bet is you will.
 
M

Muneer Mikel

Eric,

I did include those field in the "tblMain" for a reason. These few
attributes will not change for a member, however, the other medical info
might change. This is why they are included in the "tblMedical" because at
the end of the year they'll be deleted.
As a test, I deleted the "patient info" from the query, and left only
"medical info" (which they are coming from the "tblMoreMedicalInfo". My query
results were wrong again. Then I kept relocating the "Is Not Null" everytime
for all the fields and check the results. Finally, I got the right results
when I put the "Is Not Null" in one line for all the fields from one table,
and put the fields from the other table on the following line.

Thank you so much for your help
Muneer
 
Top