Selecting records that show in a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables, one containing a property address and the other containing
clients. There is a linking table that links clients to addresses, as there
is usually more than one client at an address.

The client table includes a Yes/No checkbox field.

I want a report that will show me all the properties where the checkbox
field is NO for all of the clients linked to that property. The report needs
to exclude properties where the checkbox is YES for some or all of the
clients linked to the property.
 
Stephen,
In the query behind the report, against the checkbox field, use a
criteria of...
=False
 
Thanks for this but I don't think that it achieves what I am seeking to do.
This would exclude all the clients where the checkbox is TRUE, but if you had
a property where the checkbox was true for some of the clients but false for
others that property would still show up in the report.

I want a solution that will look at each client attached to a particular
property and then list that property only if ALL the clients attached to the
property have the checkbox set to TRUE.


Stephen Brown
 
Stephen, create a query that uses both tables.

Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

Drag the primary key from the Property table into the grid, and any other
fields you want from this table. Accept Group By in the Total row under
these fields

Drag the Yes/No field from the Client table into the grid.
In the Total row under this field, choose Sum.
In the Criteria row, enter:
=0

The query will now show only properties that have clients attached (the
inner join does that), but not if any client has the yes/no field selected.

Access uses 0 for False, and -1 for True.
Summing the yes/no field gives zero if all records are false.

If you actually wanted all clients True (rather than all clients False), in
the Field row enter:
Not [MyYesNo]
instead MyYesNo.

Results will be read-only.
 
Many thanks - I managed to get what I wanted based on your reply. For some
reaosn when I did what you suggested on the actual yes/no field it created an
ODBC call failure when I ran the query, but I got it to work by creating a
separate expression that simply duplicated the value of the yes/no field.




Allen Browne said:
Stephen, create a query that uses both tables.

Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

Drag the primary key from the Property table into the grid, and any other
fields you want from this table. Accept Group By in the Total row under
these fields

Drag the Yes/No field from the Client table into the grid.
In the Total row under this field, choose Sum.
In the Criteria row, enter:
=0

The query will now show only properties that have clients attached (the
inner join does that), but not if any client has the yes/no field selected.

Access uses 0 for False, and -1 for True.
Summing the yes/no field gives zero if all records are false.

If you actually wanted all clients True (rather than all clients False), in
the Field row enter:
Not [MyYesNo]
instead MyYesNo.

Results will be read-only.

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

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

Stephen Brown said:
Thanks for this but I don't think that it achieves what I am seeking to
do.
This would exclude all the clients where the checkbox is TRUE, but if you
had
a property where the checkbox was true for some of the clients but false
for
others that property would still show up in the report.

I want a solution that will look at each client attached to a particular
property and then list that property only if ALL the clients attached to
the
property have the checkbox set to TRUE.
Click to expand...
Click to expand...
 
Interesting: perhaps you are linked to a database that allows nulls in a
yes/no field. That is technically correct, but Access (JET) can't cope with
it, so your logical expression may have worked around it.

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

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

Stephen Brown said:
Many thanks - I managed to get what I wanted based on your reply. For
some
reaosn when I did what you suggested on the actual yes/no field it created
an
ODBC call failure when I ran the query, but I got it to work by creating a
separate expression that simply duplicated the value of the yes/no field.




Allen Browne said:
Stephen, create a query that uses both tables.

Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

Drag the primary key from the Property table into the grid, and any other
fields you want from this table. Accept Group By in the Total row under
these fields

Drag the Yes/No field from the Client table into the grid.
In the Total row under this field, choose Sum.
In the Criteria row, enter:
=0

The query will now show only properties that have clients attached (the
inner join does that), but not if any client has the yes/no field
selected.

Access uses 0 for False, and -1 for True.
Summing the yes/no field gives zero if all records are false.

If you actually wanted all clients True (rather than all clients False),
in
the Field row enter:
Not [MyYesNo]
instead MyYesNo.

Results will be read-only.

Stephen Brown said:
Thanks for this but I don't think that it achieves what I am seeking to
do.
This would exclude all the clients where the checkbox is TRUE, but if
you
had
a property where the checkbox was true for some of the clients but
false
for
others that property would still show up in the report.

I want a solution that will look at each client attached to a
particular
property and then list that property only if ALL the clients attached
to
the
property have the checkbox set to TRUE.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top