Using Yes/No data type

P

pam

This is the query I've created to capture clients who have requested repairs
and have received them and also requested repairs and not received them. I
used a yes/no data type as the indicator. When I run the query all of the
entries in the DB show up. 1. How do I display only the entries where either
situation has been checked? 2. Also, Is there a way to get a count of who has
requested repairs and received them and who has requested repairs and not
received them with yes/no as the indicator?

SELECT [TableName].ClientID, [TableName].[First Name], [TableName].[Last
Name], [TableName].Address, [TableName].AptNo, [TableName].City,
[TableName].State, [TableName].RepairRequestDate, [TableName].[Repairs
Req/Rec'd], [TableName].DateRepairsCompleted, [TableName].[Repairs Req/Not
Rec'd], [TableName].[Type of Repair]
FROM [TableName];
 
S

Steve.C.Hutchinson

This is the query I've created to capture clients who have requested repairs
and have received them and also requested repairs and not received them. I
used a yes/no data type as the indicator. When I run the query all of the
entries in the DB show up. 1. How do I display only the entries where either
situation has been checked? 2. Also, Is there a way to get a count of whohas
requested repairs and received them and who has requested repairs and not
received them with yes/no as the indicator?

SELECT [TableName].ClientID, [TableName].[First Name], [TableName].[Last
Name], [TableName].Address, [TableName].AptNo, [TableName].City,
[TableName].State, [TableName].RepairRequestDate, [TableName].[Repairs
Req/Rec'd], [TableName].DateRepairsCompleted, [TableName].[Repairs Req/Not
Rec'd], [TableName].[Type of Repair]
FROM [TableName];
Click to expand...

Pam,
You need to add some Where clauses to your query. This example has
two Yes/No fields, OnMbs? and OnOsp?:
WHERE ((([tvb0904-00TechmanConfigs].DpnDHC) Is Null) And
((t0910ModelMaster.[OnMbs?]) = True)) Or
((([tvb0904-00TechmanConfigs].DpnDHC) Is Null) And ((t0910ModelMaster.
[OnOsp?]) = True))
To make this Or work in the query pane you need to use separate lines
in the Criteria section for your Yes/No fields Criteria.
 
P

pam

Sorry, Steve but I don't understand what you're telling me to do? As a result
of what I think you're saying I get: The expression you entered contains
invalid syntax.

[email protected] said:
This is the query I've created to capture clients who have requested repairs
and have received them and also requested repairs and not received them. I
used a yes/no data type as the indicator. When I run the query all of the
entries in the DB show up. 1. How do I display only the entries where either
situation has been checked? 2. Also, Is there a way to get a count of who has
requested repairs and received them and who has requested repairs and not
received them with yes/no as the indicator?

SELECT [TableName].ClientID, [TableName].[First Name], [TableName].[Last
Name], [TableName].Address, [TableName].AptNo, [TableName].City,
[TableName].State, [TableName].RepairRequestDate, [TableName].[Repairs
Req/Rec'd], [TableName].DateRepairsCompleted, [TableName].[Repairs Req/Not
Rec'd], [TableName].[Type of Repair]
FROM [TableName];
Click to expand...

Pam,
You need to add some Where clauses to your query. This example has
two Yes/No fields, OnMbs? and OnOsp?:
WHERE ((([tvb0904-00TechmanConfigs].DpnDHC) Is Null) And
((t0910ModelMaster.[OnMbs?]) = True)) Or
((([tvb0904-00TechmanConfigs].DpnDHC) Is Null) And ((t0910ModelMaster.
[OnOsp?]) = True))
To make this Or work in the query pane you need to use separate lines
in the Criteria section for your Yes/No fields Criteria.
Click to expand...
 
J

John Spencer

ASSUMPTION: [TableName].[Repairs Req/Rec'd]
and [TableName].[Repairs Req/Not Rec'd] are both Yes/No (boolean) fields.

This should return the list of Requested - not received.
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Not Rec'd] = True

This should return the list of requested/received
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Rec'd] = True

This gives you a count of received and not recieved
Select Abs(Sum([TableName].[Repairs Req/Rec'd])) as CountReceived
, Abs(Sum([TableName].[Repairs Req/Not Rec'd])) as CountNotReceived
FROM [TableName]

Those two fields can probably be calculated based on the following
[TableName].[Repairs Req/Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is not null
and
[TableName].[Repairs Req/Not Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
P

pam

John, I'm getting syntax errors. Initially, for the sake of testing, I
checked two clients one Received repairs and one did not receive repairs. The
initial set up of the query all the clients were indicated as a result of
running the query (whether they requested repairs or not). Now there is only
one row showing with no data.

John Spencer said:
ASSUMPTION: [TableName].[Repairs Req/Rec'd]
and [TableName].[Repairs Req/Not Rec'd] are both Yes/No (boolean) fields.

This should return the list of Requested - not received.
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Not Rec'd] = True

This should return the list of requested/received
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Rec'd] = True

This gives you a count of received and not recieved
Select Abs(Sum([TableName].[Repairs Req/Rec'd])) as CountReceived
, Abs(Sum([TableName].[Repairs Req/Not Rec'd])) as CountNotReceived
FROM [TableName]

Those two fields can probably be calculated based on the following
[TableName].[Repairs Req/Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is not null
and
[TableName].[Repairs Req/Not Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
This is the query I've created to capture clients who have requested repairs
and have received them and also requested repairs and not received them. I
used a yes/no data type as the indicator. When I run the query all of the
entries in the DB show up. 1. How do I display only the entries where either
situation has been checked? 2. Also, Is there a way to get a count of who has
requested repairs and received them and who has requested repairs and not
received them with yes/no as the indicator?

SELECT [TableName].ClientID, [TableName].[First Name], [TableName].[Last
Name], [TableName].Address, [TableName].AptNo, [TableName].City,
[TableName].State, [TableName].RepairRequestDate, [TableName].[Repairs
Req/Rec'd], [TableName].DateRepairsCompleted, [TableName].[Repairs Req/Not
Rec'd], [TableName].[Type of Repair]
FROM [TableName];
Click to expand...
Click to expand...
 
P

pam

Maybe I'm going about this the wrong way and using the Yes/No data type is
not the way to go. Can you suggest an expression that will give me names and
addresses of clients who have requested repairs and have not received them as
well as clients who have received repairs only - based on the SQL I
provided?? Level of frustration is reaching critical. Have to solve this by
Friday.

John Spencer said:
ASSUMPTION: [TableName].[Repairs Req/Rec'd]
and [TableName].[Repairs Req/Not Rec'd] are both Yes/No (boolean) fields.

This should return the list of Requested - not received.
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Not Rec'd] = True

This should return the list of requested/received
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Rec'd] = True

This gives you a count of received and not recieved
Select Abs(Sum([TableName].[Repairs Req/Rec'd])) as CountReceived
, Abs(Sum([TableName].[Repairs Req/Not Rec'd])) as CountNotReceived
FROM [TableName]

Those two fields can probably be calculated based on the following
[TableName].[Repairs Req/Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is not null
and
[TableName].[Repairs Req/Not Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
This is the query I've created to capture clients who have requested repairs
and have received them and also requested repairs and not received them. I
used a yes/no data type as the indicator. When I run the query all of the
entries in the DB show up. 1. How do I display only the entries where either
situation has been checked? 2. Also, Is there a way to get a count of who has
requested repairs and received them and who has requested repairs and not
received them with yes/no as the indicator?

SELECT [TableName].ClientID, [TableName].[First Name], [TableName].[Last
Name], [TableName].Address, [TableName].AptNo, [TableName].City,
[TableName].State, [TableName].RepairRequestDate, [TableName].[Repairs
Req/Rec'd], [TableName].DateRepairsCompleted, [TableName].[Repairs Req/Not
Rec'd], [TableName].[Type of Repair]
FROM [TableName];
Click to expand...
Click to expand...
 
J

John Spencer

I'm assuming that you only used one of the queries I posted.

If you are trying to return both sets of conditions in one query change the
where clause to

WHERE [TableName].[Repairs Req/Not Rec'd] = True OR
[TableName].[Repairs Req/Rec'd] = True

If you are getting syntax errors, you will have to work out why. I can't see
the actual query you are attempting to execute.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John, I'm getting syntax errors. Initially, for the sake of testing, I
checked two clients one Received repairs and one did not receive repairs. The
initial set up of the query all the clients were indicated as a result of
running the query (whether they requested repairs or not). Now there is only
one row showing with no data.

John Spencer said:
ASSUMPTION: [TableName].[Repairs Req/Rec'd]
and [TableName].[Repairs Req/Not Rec'd] are both Yes/No (boolean) fields.

This should return the list of Requested - not received.
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Not Rec'd] = True

This should return the list of requested/received
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Rec'd] = True

This gives you a count of received and not recieved
Select Abs(Sum([TableName].[Repairs Req/Rec'd])) as CountReceived
, Abs(Sum([TableName].[Repairs Req/Not Rec'd])) as CountNotReceived
FROM [TableName]

Those two fields can probably be calculated based on the following
[TableName].[Repairs Req/Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is not null
and
[TableName].[Repairs Req/Not Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
This is the query I've created to capture clients who have requested repairs
and have received them and also requested repairs and not received them. I
used a yes/no data type as the indicator. When I run the query all of the
entries in the DB show up. 1. How do I display only the entries where either
situation has been checked? 2. Also, Is there a way to get a count of who has
requested repairs and received them and who has requested repairs and not
received them with yes/no as the indicator?

SELECT [TableName].ClientID, [TableName].[First Name], [TableName].[Last
Name], [TableName].Address, [TableName].AptNo, [TableName].City,
[TableName].State, [TableName].RepairRequestDate, [TableName].[Repairs
Req/Rec'd], [TableName].DateRepairsCompleted, [TableName].[Repairs Req/Not
Rec'd], [TableName].[Type of Repair]
FROM [TableName];
Click to expand...
Click to expand...
Click to expand...
 
P

pam

Thank you so much John.... IT WORKED!!!

John Spencer said:
I'm assuming that you only used one of the queries I posted.

If you are trying to return both sets of conditions in one query change the
where clause to

WHERE [TableName].[Repairs Req/Not Rec'd] = True OR
[TableName].[Repairs Req/Rec'd] = True

If you are getting syntax errors, you will have to work out why. I can't see
the actual query you are attempting to execute.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
John, I'm getting syntax errors. Initially, for the sake of testing, I
checked two clients one Received repairs and one did not receive repairs. The
initial set up of the query all the clients were indicated as a result of
running the query (whether they requested repairs or not). Now there is only
one row showing with no data.

John Spencer said:
ASSUMPTION: [TableName].[Repairs Req/Rec'd]
and [TableName].[Repairs Req/Not Rec'd] are both Yes/No (boolean) fields.

This should return the list of Requested - not received.
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Not Rec'd] = True

This should return the list of requested/received
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Rec'd] = True

This gives you a count of received and not recieved
Select Abs(Sum([TableName].[Repairs Req/Rec'd])) as CountReceived
, Abs(Sum([TableName].[Repairs Req/Not Rec'd])) as CountNotReceived
FROM [TableName]

Those two fields can probably be calculated based on the following
[TableName].[Repairs Req/Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is not null
and
[TableName].[Repairs Req/Not Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

pam wrote:
This is the query I've created to capture clients who have requested repairs
and have received them and also requested repairs and not received them. I
used a yes/no data type as the indicator. When I run the query all of the
entries in the DB show up. 1. How do I display only the entries where either
situation has been checked? 2. Also, Is there a way to get a count of who has
requested repairs and received them and who has requested repairs and not
received them with yes/no as the indicator?

SELECT [TableName].ClientID, [TableName].[First Name], [TableName].[Last
Name], [TableName].Address, [TableName].AptNo, [TableName].City,
[TableName].State, [TableName].RepairRequestDate, [TableName].[Repairs
Req/Rec'd], [TableName].DateRepairsCompleted, [TableName].[Repairs Req/Not
Rec'd], [TableName].[Type of Repair]
FROM [TableName];
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Requested repair but repair not done based on there is a request date but
there is no completed date.

SELECT [TableName].ClientID
, [TableName].[First Name]
, [TableName].[Last Name]
, [TableName].Address
, [TableName].AptNo
, [TableName].City
, [TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[RepairRequestDate] is not Null
AND [TableName].DateRepairsCompleted is Null


Requested repair and repair is done based on request date and completed date
both being there.
SELECT [TableName].ClientID
, [TableName].[First Name]
, [TableName].[Last Name]
, [TableName].Address
, [TableName].AptNo
, [TableName].City
, [TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[RepairRequestDate] is not Null
AND [TableName].DateRepairsCompleted is Not Null

Your database is probably not well-designed. You should have a table of
clients and another table for requested repairs. The client table would have
the client information - name, address, phone numbers etc.

The RequestedRepairs table would have the clientid and RequestDate,
CompletedDate, TypeOfRepair. And perhaps an AssignedTo field and a
CurrentStatus field (In Progress, On hold, awaiting parts, completed, etc.)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Maybe I'm going about this the wrong way and using the Yes/No data type is
not the way to go. Can you suggest an expression that will give me names and
addresses of clients who have requested repairs and have not received them as
well as clients who have received repairs only - based on the SQL I
provided?? Level of frustration is reaching critical. Have to solve this by
Friday.

John Spencer said:
ASSUMPTION: [TableName].[Repairs Req/Rec'd]
and [TableName].[Repairs Req/Not Rec'd] are both Yes/No (boolean) fields.

This should return the list of Requested - not received.
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Not Rec'd] = True

This should return the list of requested/received
SELECT [TableName].ClientID, [TableName].[First Name]
, [TableName].[Last Name], [TableName].Address, [TableName].AptNo,
[TableName].City,
[TableName].State
, [TableName].RepairRequestDate
, [TableName].[Repairs Req/Rec'd]
, [TableName].DateRepairsCompleted
, [TableName].[Repairs Req/Not Rec'd]
, [TableName].[Type of Repair]
FROM [TableName]
WHERE [TableName].[Repairs Req/Rec'd] = True

This gives you a count of received and not recieved
Select Abs(Sum([TableName].[Repairs Req/Rec'd])) as CountReceived
, Abs(Sum([TableName].[Repairs Req/Not Rec'd])) as CountNotReceived
FROM [TableName]

Those two fields can probably be calculated based on the following
[TableName].[Repairs Req/Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is not null
and
[TableName].[Repairs Req/Not Rec'd]
RepairRequestDate is Not Null and DateRepairsCompleted is null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
This is the query I've created to capture clients who have requested repairs
and have received them and also requested repairs and not received them. I
used a yes/no data type as the indicator. When I run the query all of the
entries in the DB show up. 1. How do I display only the entries where either
situation has been checked? 2. Also, Is there a way to get a count of who has
requested repairs and received them and who has requested repairs and not
received them with yes/no as the indicator?

SELECT [TableName].ClientID, [TableName].[First Name], [TableName].[Last
Name], [TableName].Address, [TableName].AptNo, [TableName].City,
[TableName].State, [TableName].RepairRequestDate, [TableName].[Repairs
Req/Rec'd], [TableName].DateRepairsCompleted, [TableName].[Repairs Req/Not
Rec'd], [TableName].[Type of Repair]
FROM [TableName];
Click to expand...
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

updating a query with data from a table 1
Working with dates in a query and . . . 13
data collection and THEN QUERY 1
Import & export tables based on table data and docmd 14
Query to populate combobox with names of tables, only if they contain data 2
Is it SQL 2008 or VB 2008 problem? I don't know 1
Changing data type causes error 6
Pivot Table type mismatch 1

Top