detail line with zeros - suppress printing?

[Susan]

I have a report with detail lines with
Name Col1 Col2 Col3 Col4 Col5 Col6 Col7

The Col's are numeric fields. I do not want to print the detail line if ALL
the Cols fields for that detail line have values that are zeros or nulls.
How would you do that?

Thanks,
Susan

 

[Allen Browne]

Assuming there are no negatives, you could create a query, and type an
expression like this into the Field row in query design:
Nz([Col1],0) + Nz([Col2],0) + Nz([Col3],0) + Nz([Col4],0) + ...
Then in the Criteria row beneath this:
<> 0

That may solve your current problem. But the real issue is that you have
built a spreasheet in Access, instead of a relational database. You really
need to have many rows in a related table, instead of repeating columns in
one table.

 

[Susan]

Thanks, actually each record has the family name and the columns represent
numeric fields that have the number of members in the family that
volunteered to help with publicity, fundraising, shelf-reading, bookstore,
etc. It is from the raw data in a table that will be entered. It can be
summed and then I will have a total of bodies that volunteered. I posted in
the other newsgroup a question about how to filter the query on the columns.
I did not want the records where one volunteered for none of the categories.
But I never got an answer or figured that one out.

I tried a query but I got hung up with the Boolean stuff. I tried to go down
to each column and on the Criteria or Or place the >0 for each field but
realized that it was the logic
If Col1 >0 and Col2>0 and Col3 >0 etc.
I was getting less and less records due to the implied 'and'--I needed the
'or' logic between fields

Please explain the Field row thing more to me. I am very new to Access. So
if I do the query I will have the Name field, Col1, Col2, Col3, Col4, Col5,
Col6, Col7 (actually I gave them more meaningfull names), where do I put the
expression? What Field? I am looking at your + below and realize that is
very important.

Thank you for your response.
Susan

Assuming there are no negatives, you could create a query, and type an
expression like this into the Field row in query design:
Nz([Col1],0) + Nz([Col2],0) + Nz([Col3],0) + Nz([Col4],0) + ...
Then in the Criteria row beneath this:
<> 0

That may solve your current problem. But the real issue is that you have
built a spreasheet in Access, instead of a relational database. You really
need to have many rows in a related table, instead of repeating columns in
one table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report with detail lines with
Name Col1 Col2 Col3 Col4 Col5 Col6 Col7

The Col's are numeric fields. I do not want to print the detail line if
ALL the Cols fields for that detail line have values that are zeros or
nulls. How would you do that?

 

[Duane Hookom]

I believe what Allen is suggesting is to change your table structure so
"publicity, fundraising, shelf-reading, bookstore, etc" become data values
rather than field names. If you add another volunteer opportunity, you should
add a record to table, NOT create a new field.

I recently created a solution for a solution for an organization that had
tables like:

tblMembers
================
MemID Primary Key Autonumber
MemFirstName
MemLastName
.....

tblVolOpps (Volunteer opportunities)
==================
OppID Primary Key Autonumber
OppTitle Values like publicity, fundraising, shelf-reading, bookstore, etc
OppActive
....

tblMemVol
=================
MVoID Primary Key Autonumber
MVoMemID links to tblMembers.MemID
MVoOppID links to tblVolOpps.OppID
MVoStartDate
MVoEndDate
MVoComments

--
Duane Hookom
Microsoft Access MVP

Thanks, actually each record has the family name and the columns represent
numeric fields that have the number of members in the family that
volunteered to help with publicity, fundraising, shelf-reading, bookstore,
etc. It is from the raw data in a table that will be entered. It can be
summed and then I will have a total of bodies that volunteered. I posted in
the other newsgroup a question about how to filter the query on the columns.
I did not want the records where one volunteered for none of the categories.
But I never got an answer or figured that one out.

I tried a query but I got hung up with the Boolean stuff. I tried to go down
to each column and on the Criteria or Or place the >0 for each field but
realized that it was the logic
If Col1 >0 and Col2>0 and Col3 >0 etc.
I was getting less and less records due to the implied 'and'--I needed the
'or' logic between fields

Please explain the Field row thing more to me. I am very new to Access. So
if I do the query I will have the Name field, Col1, Col2, Col3, Col4, Col5,
Col6, Col7 (actually I gave them more meaningfull names), where do I put the
expression? What Field? I am looking at your + below and realize that is
very important.

Thank you for your response.
Susan

Assuming there are no negatives, you could create a query, and type an
expression like this into the Field row in query design:
Nz([Col1],0) + Nz([Col2],0) + Nz([Col3],0) + Nz([Col4],0) + ...
Then in the Criteria row beneath this:
<> 0

That may solve your current problem. But the real issue is that you have
built a spreasheet in Access, instead of a relational database. You really
need to have many rows in a related table, instead of repeating columns in
one table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report with detail lines with
Name Col1 Col2 Col3 Col4 Col5 Col6 Col7

The Col's are numeric fields. I do not want to print the detail line if
ALL the Cols fields for that detail line have values that are zeros or
nulls. How would you do that?

 

[Susan]

It is a very small non-profit organization which supports the public library
about 260 members. I as membership chairperson will receive cards by mail
(not email) with name, address, volunteer categories, membership categories.
Are you telling me that I need to have separate tables set up for this
membership data which is currently in one table (keyed by autonumber and
membership date) with volunteer opportunities, membership categories, etc.?

Why can't I just print a report from that with name, phone number and the
volunteer categories (which are fields) if not zero or null?

I believe what Allen is suggesting is to change your table structure so
"publicity, fundraising, shelf-reading, bookstore, etc" become data values
rather than field names. If you add another volunteer opportunity, you
should
add a record to table, NOT create a new field.

I recently created a solution for a solution for an organization that had
tables like:

tblMembers
================
MemID Primary Key Autonumber
MemFirstName
MemLastName
....

tblVolOpps (Volunteer opportunities)
==================
OppID Primary Key Autonumber
OppTitle Values like publicity, fundraising, shelf-reading, bookstore, etc
OppActive
...

tblMemVol
=================
MVoID Primary Key Autonumber
MVoMemID links to tblMembers.MemID
MVoOppID links to tblVolOpps.OppID
MVoStartDate
MVoEndDate
MVoComments

--
Duane Hookom
Microsoft Access MVP

Thanks, actually each record has the family name and the columns
represent
numeric fields that have the number of members in the family that
volunteered to help with publicity, fundraising, shelf-reading,
bookstore,
etc. It is from the raw data in a table that will be entered. It can be
summed and then I will have a total of bodies that volunteered. I posted
in
the other newsgroup a question about how to filter the query on the
columns.
I did not want the records where one volunteered for none of the
categories.
But I never got an answer or figured that one out.

I tried a query but I got hung up with the Boolean stuff. I tried to go
down
to each column and on the Criteria or Or place the >0 for each field but
realized that it was the logic
If Col1 >0 and Col2>0 and Col3 >0 etc.
I was getting less and less records due to the implied 'and'--I needed
the
'or' logic between fields

Please explain the Field row thing more to me. I am very new to Access.
So
if I do the query I will have the Name field, Col1, Col2, Col3, Col4,
Col5,
Col6, Col7 (actually I gave them more meaningfull names), where do I put
the
expression? What Field? I am looking at your + below and realize that is
very important.

Thank you for your response.
Susan

Assuming there are no negatives, you could create a query, and type an
expression like this into the Field row in query design:
Nz([Col1],0) + Nz([Col2],0) + Nz([Col3],0) + Nz([Col4],0) + ...
Then in the Criteria row beneath this:
<> 0

That may solve your current problem. But the real issue is that you
have
built a spreasheet in Access, instead of a relational database. You
really
need to have many rows in a related table, instead of repeating columns
in
one table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report with detail lines with
Name Col1 Col2 Col3 Col4 Col5 Col6 Col7

The Col's are numeric fields. I do not want to print the detail line
if
ALL the Cols fields for that detail line have values that are zeros or
nulls. How would you do that?

 

[Duane Hookom]

You can do it your way but every time you want to set up a new volunteer
opportunity, how do you want to do it? Are you satisfied with creating a new
field, new controls, updating queries, ...?

A normalized solution provides much greater flexibility.

Are you still interested in using your current structure? If so create a new
column in your query with a column as Allen suggested. Substitute your field
names for ColX.

--
Duane Hookom
Microsoft Access MVP

It is a very small non-profit organization which supports the public library
about 260 members. I as membership chairperson will receive cards by mail
(not email) with name, address, volunteer categories, membership categories.
Are you telling me that I need to have separate tables set up for this
membership data which is currently in one table (keyed by autonumber and
membership date) with volunteer opportunities, membership categories, etc.?

Why can't I just print a report from that with name, phone number and the
volunteer categories (which are fields) if not zero or null?

I believe what Allen is suggesting is to change your table structure so
"publicity, fundraising, shelf-reading, bookstore, etc" become data values
rather than field names. If you add another volunteer opportunity, you
should
add a record to table, NOT create a new field.

I recently created a solution for a solution for an organization that had
tables like:

tblMembers
================
MemID Primary Key Autonumber
MemFirstName
MemLastName
....

tblVolOpps (Volunteer opportunities)
==================
OppID Primary Key Autonumber
OppTitle Values like publicity, fundraising, shelf-reading, bookstore, etc
OppActive
...

tblMemVol
=================
MVoID Primary Key Autonumber
MVoMemID links to tblMembers.MemID
MVoOppID links to tblVolOpps.OppID
MVoStartDate
MVoEndDate
MVoComments

--
Duane Hookom
Microsoft Access MVP

Thanks, actually each record has the family name and the columns
represent
numeric fields that have the number of members in the family that
volunteered to help with publicity, fundraising, shelf-reading,
bookstore,
etc. It is from the raw data in a table that will be entered. It can be
summed and then I will have a total of bodies that volunteered. I posted
in
the other newsgroup a question about how to filter the query on the
columns.
I did not want the records where one volunteered for none of the
categories.
But I never got an answer or figured that one out.

I tried a query but I got hung up with the Boolean stuff. I tried to go
down
to each column and on the Criteria or Or place the >0 for each field but
realized that it was the logic
If Col1 >0 and Col2>0 and Col3 >0 etc.
I was getting less and less records due to the implied 'and'--I needed
the
'or' logic between fields

Please explain the Field row thing more to me. I am very new to Access.
So
if I do the query I will have the Name field, Col1, Col2, Col3, Col4,
Col5,
Col6, Col7 (actually I gave them more meaningfull names), where do I put
the
expression? What Field? I am looking at your + below and realize that is
very important.

Thank you for your response.
Susan

Assuming there are no negatives, you could create a query, and type an
expression like this into the Field row in query design:
Nz([Col1],0) + Nz([Col2],0) + Nz([Col3],0) + Nz([Col4],0) + ...
Then in the Criteria row beneath this:
<> 0

That may solve your current problem. But the real issue is that you
have
built a spreasheet in Access, instead of a relational database. You
really
need to have many rows in a related table, instead of repeating columns
in
one table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report with detail lines with
Name Col1 Col2 Col3 Col4 Col5 Col6 Col7

The Col's are numeric fields. I do not want to print the detail line
if
ALL the Cols fields for that detail line have values that are zeros or
nulls. How would you do that?

 

[Susan]

I understand your point, right now I will receive a card in the mail, if
this changes then I will change the structure to accomodate changes. So far
everything is based on the table with the membership data and everything is
automatically updated.

I feel very stupid. I realize now I can use the Toggle filter and I did not
understand the filter and criteria and or. Now I realize I had to place the

0 on different rows.

This was great for examples.
http://office.microsoft.com/en-us/access/HA100666111033.aspx?pid=CH100645771033

You can do it your way but every time you want to set up a new volunteer
opportunity, how do you want to do it? Are you satisfied with creating a
new
field, new controls, updating queries, ...?

A normalized solution provides much greater flexibility.

Are you still interested in using your current structure? If so create a
new
column in your query with a column as Allen suggested. Substitute your
field
names for ColX.

--
Duane Hookom
Microsoft Access MVP

It is a very small non-profit organization which supports the public
library
about 260 members. I as membership chairperson will receive cards by mail
(not email) with name, address, volunteer categories, membership
categories.
Are you telling me that I need to have separate tables set up for this
membership data which is currently in one table (keyed by autonumber and
membership date) with volunteer opportunities, membership categories,
etc.?

Why can't I just print a report from that with name, phone number and the
volunteer categories (which are fields) if not zero or null?

I believe what Allen is suggesting is to change your table structure so
"publicity, fundraising, shelf-reading, bookstore, etc" become data
values
rather than field names. If you add another volunteer opportunity, you
should
add a record to table, NOT create a new field.

I recently created a solution for a solution for an organization that
had
tables like:

tblMembers
================
MemID Primary Key Autonumber
MemFirstName
MemLastName
....

tblVolOpps (Volunteer opportunities)
==================
OppID Primary Key Autonumber
OppTitle Values like publicity, fundraising, shelf-reading, bookstore,
etc
OppActive
...

tblMemVol
=================
MVoID Primary Key Autonumber
MVoMemID links to tblMembers.MemID
MVoOppID links to tblVolOpps.OppID
MVoStartDate
MVoEndDate
MVoComments

--
Duane Hookom
Microsoft Access MVP


:

Thanks, actually each record has the family name and the columns
represent
numeric fields that have the number of members in the family that
volunteered to help with publicity, fundraising, shelf-reading,
bookstore,
etc. It is from the raw data in a table that will be entered. It can
be
summed and then I will have a total of bodies that volunteered. I
posted
in
the other newsgroup a question about how to filter the query on the
columns.
I did not want the records where one volunteered for none of the
categories.
But I never got an answer or figured that one out.

I tried a query but I got hung up with the Boolean stuff. I tried to
go
down
to each column and on the Criteria or Or place the >0 for each field
but
realized that it was the logic
If Col1 >0 and Col2>0 and Col3 >0 etc.
I was getting less and less records due to the implied 'and'--I needed
the
'or' logic between fields

Please explain the Field row thing more to me. I am very new to
Access.
So
if I do the query I will have the Name field, Col1, Col2, Col3, Col4,
Col5,
Col6, Col7 (actually I gave them more meaningfull names), where do I
put
the
expression? What Field? I am looking at your + below and realize that
is
very important.

Thank you for your response.
Susan

Assuming there are no negatives, you could create a query, and type
an
expression like this into the Field row in query design:
Nz([Col1],0) + Nz([Col2],0) + Nz([Col3],0) + Nz([Col4],0) + ...
Then in the Criteria row beneath this:
<> 0

That may solve your current problem. But the real issue is that you
have
built a spreasheet in Access, instead of a relational database. You
really
need to have many rows in a related table, instead of repeating
columns
in
one table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report with detail lines with
Name Col1 Col2 Col3 Col4 Col5 Col6 Col7

The Col's are numeric fields. I do not want to print the detail
line
if
ALL the Cols fields for that detail line have values that are zeros
or
nulls. How would you do that?

 

[Duane Hookom]

What's printed on a card should never be what determines your table structure.

Are we to assume you have your issue resolved (at least until you add
another volunteer opportunity ;-)?
--
Duane Hookom
Microsoft Access MVP

I understand your point, right now I will receive a card in the mail, if
this changes then I will change the structure to accomodate changes. So far
everything is based on the table with the membership data and everything is
automatically updated.

I feel very stupid. I realize now I can use the Toggle filter and I did not
understand the filter and criteria and or. Now I realize I had to place the

0 on different rows.

This was great for examples.
http://office.microsoft.com/en-us/access/HA100666111033.aspx?pid=CH100645771033

You can do it your way but every time you want to set up a new volunteer
opportunity, how do you want to do it? Are you satisfied with creating a
new
field, new controls, updating queries, ...?

A normalized solution provides much greater flexibility.

Are you still interested in using your current structure? If so create a
new
column in your query with a column as Allen suggested. Substitute your
field
names for ColX.

--
Duane Hookom
Microsoft Access MVP

It is a very small non-profit organization which supports the public
library
about 260 members. I as membership chairperson will receive cards by mail
(not email) with name, address, volunteer categories, membership
categories.
Are you telling me that I need to have separate tables set up for this
membership data which is currently in one table (keyed by autonumber and
membership date) with volunteer opportunities, membership categories,
etc.?

Why can't I just print a report from that with name, phone number and the
volunteer categories (which are fields) if not zero or null?



I believe what Allen is suggesting is to change your table structure so
"publicity, fundraising, shelf-reading, bookstore, etc" become data
values
rather than field names. If you add another volunteer opportunity, you
should
add a record to table, NOT create a new field.

I recently created a solution for a solution for an organization that
had
tables like:

tblMembers
================
MemID Primary Key Autonumber
MemFirstName
MemLastName
....

tblVolOpps (Volunteer opportunities)
==================
OppID Primary Key Autonumber
OppTitle Values like publicity, fundraising, shelf-reading, bookstore,
etc
OppActive
...

tblMemVol
=================
MVoID Primary Key Autonumber
MVoMemID links to tblMembers.MemID
MVoOppID links to tblVolOpps.OppID
MVoStartDate
MVoEndDate
MVoComments

--
Duane Hookom
Microsoft Access MVP


:

Thanks, actually each record has the family name and the columns
represent
numeric fields that have the number of members in the family that
volunteered to help with publicity, fundraising, shelf-reading,
bookstore,
etc. It is from the raw data in a table that will be entered. It can
be
summed and then I will have a total of bodies that volunteered. I
posted
in
the other newsgroup a question about how to filter the query on the
columns.
I did not want the records where one volunteered for none of the
categories.
But I never got an answer or figured that one out.

I tried a query but I got hung up with the Boolean stuff. I tried to
go
down
to each column and on the Criteria or Or place the >0 for each field
but
realized that it was the logic
If Col1 >0 and Col2>0 and Col3 >0 etc.
I was getting less and less records due to the implied 'and'--I needed
the
'or' logic between fields

Please explain the Field row thing more to me. I am very new to
Access.
So
if I do the query I will have the Name field, Col1, Col2, Col3, Col4,
Col5,
Col6, Col7 (actually I gave them more meaningfull names), where do I
put
the
expression? What Field? I am looking at your + below and realize that
is
very important.

Thank you for your response.
Susan

Assuming there are no negatives, you could create a query, and type
an
expression like this into the Field row in query design:
Nz([Col1],0) + Nz([Col2],0) + Nz([Col3],0) + Nz([Col4],0) + ...
Then in the Criteria row beneath this:
<> 0

That may solve your current problem. But the real issue is that you
have
built a spreasheet in Access, instead of a relational database. You
really
need to have many rows in a related table, instead of repeating
columns
in
one table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a report with detail lines with
Name Col1 Col2 Col3 Col4 Col5 Col6 Col7

The Col's are numeric fields. I do not want to print the detail
line
if
ALL the Cols fields for that detail line have values that are zeros
or
nulls. How would you do that?

 

[Allen Browne]

Susan, I see that Duane has given you more detail on how to design this
well.

Feel free to just stay with what you understand, but the interface would end
up being a form where you can enter the information for a person. The form
would have a subform for the areas they wish to volunteer for. You add as
many rows to the subform as you need.

All the best