Yes/No or None

G

Guest

Hi. I have a yes/no field for Coordinator. Each program has one
coordinator. I'm trying to figure out a query to find programs that have no
coordinator, but because I used yes/no, I can't seem to get what I want. If
I use "no" in the query, I get EVERYONE who isn't a coordinator (even if
there is a coordinator for the program).

Any suggestions? I've posted the queries I'm working from (I tried to trim
them, but ended up posting the whole thing so I don't cut anything important):

Here's the subquery:
SELECT Event.EventVolunteerID, Organizations.OrganizationName,
Volunteering.VolunteerName, Organizations.OrganizationState,
Frequency.Frequency, Volunteering.VolunteerREAD, Frequency.FrequencyFactor,
Volunteering.VolunteerEndDate, Volunteering.VolunteerStartDate
FROM (Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes))
ORDER BY Volunteering.VolunteerDay DESC;

Here's the query:
SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
ORDER BY Contacts.LastName, Contacts.LastName;

I'd appreciate any suggestions on how I can end up with OrganizationName,
Volunteering.VolunteerName with no one in them as a Coordinator.
 
G

Guest

Create a table with fields "yes', "no", and "none". Create a query from this
table showing all fields. Then in your form use a Combo box based on the
query. This will allow you to make a selection of yes, no, or none.
 
G

Guest

Sam,
Interesting! Thanks for the reply.
I only want yes/no (that's why I have the check box). What I'm looking for
here is for where we've forgotten to select a Coordinator for the program. I
don't imagine it will happen often. I just want to make sure that all
program have an assigned Corrdinator, not give an option to have "none".

Hope that makes sense. Any suggestions?


Sam said:
Create a table with fields "yes', "no", and "none". Create a query from this
table showing all fields. Then in your form use a Combo box based on the
query. This will allow you to make a selection of yes, no, or none.


Stephanie said:
Hi. I have a yes/no field for Coordinator. Each program has one
coordinator. I'm trying to figure out a query to find programs that have no
coordinator, but because I used yes/no, I can't seem to get what I want. If
I use "no" in the query, I get EVERYONE who isn't a coordinator (even if
there is a coordinator for the program).

Any suggestions? I've posted the queries I'm working from (I tried to trim
them, but ended up posting the whole thing so I don't cut anything important):

Here's the subquery:
SELECT Event.EventVolunteerID, Organizations.OrganizationName,
Volunteering.VolunteerName, Organizations.OrganizationState,
Frequency.Frequency, Volunteering.VolunteerREAD, Frequency.FrequencyFactor,
Volunteering.VolunteerEndDate, Volunteering.VolunteerStartDate
FROM (Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes))
ORDER BY Volunteering.VolunteerDay DESC;

Here's the query:
SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
ORDER BY Contacts.LastName, Contacts.LastName;

I'd appreciate any suggestions on how I can end up with OrganizationName,
Volunteering.VolunteerName with no one in them as a Coordinator.
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

I would say that you need to check if the Coordinator field is True and if
whatever field you store the name of the coordinator is null.

As a guess, is it VolunteerName that is the coordinator?

SELECT Event.ContactID,
Nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator,
[Program Opportunities subqry].OrganizationName,
[Program Opportunities subqry].VolunteerName,
[Program Opportunities subqry].Frequency,
[Program Opportunities subqry].OrganizationState,
[Program Opportunities subqry].VolunteerREAD,
[Program Opportunities subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE Event.Coordinator = True and VolunteerName is Null
ORDER BY Contacts.LastName, Contacts.LastName;
Hi. I have a yes/no field for Coordinator. Each program has one
coordinator. I'm trying to figure out a query to find programs that have no
coordinator, but because I used yes/no, I can't seem to get what I want. If
I use "no" in the query, I get EVERYONE who isn't a coordinator (even if
there is a coordinator for the program).

Any suggestions? I've posted the queries I'm working from (I tried to trim
them, but ended up posting the whole thing so I don't cut anything important):

Here's the subquery:
SELECT Event.EventVolunteerID, Organizations.OrganizationName,
Volunteering.VolunteerName, Organizations.OrganizationState,
Frequency.Frequency, Volunteering.VolunteerREAD, Frequency.FrequencyFactor,
Volunteering.VolunteerEndDate, Volunteering.VolunteerStartDate
FROM (Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes))
ORDER BY Volunteering.VolunteerDay DESC;

Here's the query:
SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
ORDER BY Contacts.LastName, Contacts.LastName;

I'd appreciate any suggestions on how I can end up with OrganizationName,
Volunteering.VolunteerName with no one in them as a Coordinator.
Click to expand...
 
G

Guest

John, Thanks for the reply. The name of the Coordinator is Contacts.LastName
and Contacts.FirstName or Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name]. What is "true" looking for?

I tried the query using Coordinator as True and Member Name as Is Null, but
the query returned nothing although I do have a program without a
Coordinator. (I threw in the "Past" field in case I had a Coordinator in the
Past) Here's what I have:

SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE (((Contacts.LastName) Is Null) AND ((Event.Past)=No) AND
((Event.Coordinator)=True))
ORDER BY Contacts.LastName;

The queries that I've played with return every variation except the one I
need! Any suggestion? Thanks.


John Spencer (MVP) said:
I would say that you need to check if the Coordinator field is True and if
whatever field you store the name of the coordinator is null.

As a guess, is it VolunteerName that is the coordinator?

SELECT Event.ContactID,
Nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator,
[Program Opportunities subqry].OrganizationName,
[Program Opportunities subqry].VolunteerName,
[Program Opportunities subqry].Frequency,
[Program Opportunities subqry].OrganizationState,
[Program Opportunities subqry].VolunteerREAD,
[Program Opportunities subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE Event.Coordinator = True and VolunteerName is Null
ORDER BY Contacts.LastName, Contacts.LastName;
Hi. I have a yes/no field for Coordinator. Each program has one
coordinator. I'm trying to figure out a query to find programs that have no
coordinator, but because I used yes/no, I can't seem to get what I want. If
I use "no" in the query, I get EVERYONE who isn't a coordinator (even if
there is a coordinator for the program).

Any suggestions? I've posted the queries I'm working from (I tried to trim
them, but ended up posting the whole thing so I don't cut anything important):

Here's the subquery:
SELECT Event.EventVolunteerID, Organizations.OrganizationName,
Volunteering.VolunteerName, Organizations.OrganizationState,
Frequency.Frequency, Volunteering.VolunteerREAD, Frequency.FrequencyFactor,
Volunteering.VolunteerEndDate, Volunteering.VolunteerStartDate
FROM (Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes))
ORDER BY Volunteering.VolunteerDay DESC;

Here's the query:
SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
ORDER BY Contacts.LastName, Contacts.LastName;

I'd appreciate any suggestions on how I can end up with OrganizationName,
Volunteering.VolunteerName with no one in them as a Coordinator.
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

Ok, since I'm not sure of your fields etc.

Is Event.Coordinator a yes/no field? If it is (it may be represented by a
check box) when you view it. Do you check it to indicate that the person -
member name - is the coordinator?

If so, then see if you get any records when you use ONLY the criteria
Event.Coordinator = True

Now, try ONLY
Contacts.LastName is Null

If those both give you the expected results then try both criteria with AND
between them. The other possibility is that Last Name is not null but an empty string.

Try
Contacts.LastName <> ""
as criteria if Contacts.LastName is Null doesn't give you the expected results.

John, Thanks for the reply. The name of the Coordinator is Contacts.LastName
and Contacts.FirstName or Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name]. What is "true" looking for?

I tried the query using Coordinator as True and Member Name as Is Null, but
the query returned nothing although I do have a program without a
Coordinator. (I threw in the "Past" field in case I had a Coordinator in the
Past) Here's what I have:

SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE (((Contacts.LastName) Is Null) AND ((Event.Past)=No) AND
((Event.Coordinator)=True))
ORDER BY Contacts.LastName;

The queries that I've played with return every variation except the one I
need! Any suggestion? Thanks.

John Spencer (MVP) said:
I would say that you need to check if the Coordinator field is True and if
whatever field you store the name of the coordinator is null.

As a guess, is it VolunteerName that is the coordinator?

SELECT Event.ContactID,
Nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator,
[Program Opportunities subqry].OrganizationName,
[Program Opportunities subqry].VolunteerName,
[Program Opportunities subqry].Frequency,
[Program Opportunities subqry].OrganizationState,
[Program Opportunities subqry].VolunteerREAD,
[Program Opportunities subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE Event.Coordinator = True and VolunteerName is Null
ORDER BY Contacts.LastName, Contacts.LastName;
Hi. I have a yes/no field for Coordinator. Each program has one
coordinator. I'm trying to figure out a query to find programs that have no
coordinator, but because I used yes/no, I can't seem to get what I want. If
I use "no" in the query, I get EVERYONE who isn't a coordinator (even if
there is a coordinator for the program).

Any suggestions? I've posted the queries I'm working from (I tried to trim
them, but ended up posting the whole thing so I don't cut anything important):

Here's the subquery:
SELECT Event.EventVolunteerID, Organizations.OrganizationName,
Volunteering.VolunteerName, Organizations.OrganizationState,
Frequency.Frequency, Volunteering.VolunteerREAD, Frequency.FrequencyFactor,
Volunteering.VolunteerEndDate, Volunteering.VolunteerStartDate
FROM (Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes))
ORDER BY Volunteering.VolunteerDay DESC;

Here's the query:
SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
ORDER BY Contacts.LastName, Contacts.LastName;

I'd appreciate any suggestions on how I can end up with OrganizationName,
Volunteering.VolunteerName with no one in them as a Coordinator.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

John,

Event.Coordinator is a yes/no and with only Event.Coordinator = True I get
back a list of all programs that have a Coordinator.

Contacts.LastName is Null brings back nothing, grayed out results. This is
also correct because I'm not looking for where a program doesn't have an
associated Contact.

My concern is that I have a program out there with Contacts assigned to it,
but none of the Contacts have been assigned as the Coordinator. So I'm
trying to run a query that shows either ALL of the programs- those with a
designated Coordinator and those without. Or the programs that have assigned
Contacts, but no assigned Coordinator.

Any suggestions?


John Spencer (MVP) said:
Ok, since I'm not sure of your fields etc.

Is Event.Coordinator a yes/no field? If it is (it may be represented by a
check box) when you view it. Do you check it to indicate that the person -
member name - is the coordinator?

If so, then see if you get any records when you use ONLY the criteria
Event.Coordinator = True

Now, try ONLY
Contacts.LastName is Null

If those both give you the expected results then try both criteria with AND
between them. The other possibility is that Last Name is not null but an empty string.

Try
Contacts.LastName <> ""
as criteria if Contacts.LastName is Null doesn't give you the expected results.

John, Thanks for the reply. The name of the Coordinator is Contacts.LastName
and Contacts.FirstName or Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name]. What is "true" looking for?

I tried the query using Coordinator as True and Member Name as Is Null, but
the query returned nothing although I do have a program without a
Coordinator. (I threw in the "Past" field in case I had a Coordinator in the
Past) Here's what I have:

SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE (((Contacts.LastName) Is Null) AND ((Event.Past)=No) AND
((Event.Coordinator)=True))
ORDER BY Contacts.LastName;

The queries that I've played with return every variation except the one I
need! Any suggestion? Thanks.

John Spencer (MVP) said:
I would say that you need to check if the Coordinator field is True and if
whatever field you store the name of the coordinator is null.

As a guess, is it VolunteerName that is the coordinator?

SELECT Event.ContactID,
Nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator,
[Program Opportunities subqry].OrganizationName,
[Program Opportunities subqry].VolunteerName,
[Program Opportunities subqry].Frequency,
[Program Opportunities subqry].OrganizationState,
[Program Opportunities subqry].VolunteerREAD,
[Program Opportunities subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE Event.Coordinator = True and VolunteerName is Null
ORDER BY Contacts.LastName, Contacts.LastName;

Stephanie wrote:

Hi. I have a yes/no field for Coordinator. Each program has one
coordinator. I'm trying to figure out a query to find programs that have no
coordinator, but because I used yes/no, I can't seem to get what I want. If
I use "no" in the query, I get EVERYONE who isn't a coordinator (even if
there is a coordinator for the program).

Any suggestions? I've posted the queries I'm working from (I tried to trim
them, but ended up posting the whole thing so I don't cut anything important):

Here's the subquery:
SELECT Event.EventVolunteerID, Organizations.OrganizationName,
Volunteering.VolunteerName, Organizations.OrganizationState,
Frequency.Frequency, Volunteering.VolunteerREAD, Frequency.FrequencyFactor,
Volunteering.VolunteerEndDate, Volunteering.VolunteerStartDate
FROM (Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes))
ORDER BY Volunteering.VolunteerDay DESC;

Here's the query:
SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
ORDER BY Contacts.LastName, Contacts.LastName;

I'd appreciate any suggestions on how I can end up with OrganizationName,
Volunteering.VolunteerName with no one in them as a Coordinator.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer (MVP)

AHHH, methinks a light just went on. Now if I can keep from extinguishing it.

Am I correct in the guess that you have multiple contacts working on an event
and one of them is the coordinator, but you are trying to identify an event that
doesn't have one of the contacts flagged as the coordinator?

If so,

Make the simplest query you can that show events with coordinator. Save that as
query one

Now, use that along with the events table in a unmatched query (there is a
wizard for this).

GENERICALLY something like

SELECT EventContacts.EventID
FROM EventContacts
WHERE EventContacts.Coordinator = True

Which would give you all the events with a coordinator. Save that as qHaveCoordinator.

SELECT *
FROM SomeTable LEFT JOIN qHaveCoordinator
ON SomeTable.EventID = qHaveCoordinator.EventID
WHERE qHaveCoordinator.EventID is NULL

Hope this is a bit clearer. IF not, will try again later. Gotta go now.
John,

Event.Coordinator is a yes/no and with only Event.Coordinator = True I get
back a list of all programs that have a Coordinator.

Contacts.LastName is Null brings back nothing, grayed out results. This is
also correct because I'm not looking for where a program doesn't have an
associated Contact.

My concern is that I have a program out there with Contacts assigned to it,
but none of the Contacts have been assigned as the Coordinator. So I'm
trying to run a query that shows either ALL of the programs- those with a
designated Coordinator and those without. Or the programs that have assigned
Contacts, but no assigned Coordinator.

Any suggestions?

John Spencer (MVP) said:
Ok, since I'm not sure of your fields etc.

Is Event.Coordinator a yes/no field? If it is (it may be represented by a
check box) when you view it. Do you check it to indicate that the person -
member name - is the coordinator?

If so, then see if you get any records when you use ONLY the criteria
Event.Coordinator = True

Now, try ONLY
Contacts.LastName is Null

If those both give you the expected results then try both criteria with AND
between them. The other possibility is that Last Name is not null but an empty string.

Try
Contacts.LastName <> ""
as criteria if Contacts.LastName is Null doesn't give you the expected results.

John, Thanks for the reply. The name of the Coordinator is Contacts.LastName
and Contacts.FirstName or Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name]. What is "true" looking for?

I tried the query using Coordinator as True and Member Name as Is Null, but
the query returned nothing although I do have a program without a
Coordinator. (I threw in the "Past" field in case I had a Coordinator in the
Past) Here's what I have:

SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE (((Contacts.LastName) Is Null) AND ((Event.Past)=No) AND
((Event.Coordinator)=True))
ORDER BY Contacts.LastName;

The queries that I've played with return every variation except the one I
need! Any suggestion? Thanks.

:

I would say that you need to check if the Coordinator field is True and if
whatever field you store the name of the coordinator is null.

As a guess, is it VolunteerName that is the coordinator?

SELECT Event.ContactID,
Nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator,
[Program Opportunities subqry].OrganizationName,
[Program Opportunities subqry].VolunteerName,
[Program Opportunities subqry].Frequency,
[Program Opportunities subqry].OrganizationState,
[Program Opportunities subqry].VolunteerREAD,
[Program Opportunities subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE Event.Coordinator = True and VolunteerName is Null
ORDER BY Contacts.LastName, Contacts.LastName;

Stephanie wrote:

Hi. I have a yes/no field for Coordinator. Each program has one
coordinator. I'm trying to figure out a query to find programs that have no
coordinator, but because I used yes/no, I can't seem to get what I want. If
I use "no" in the query, I get EVERYONE who isn't a coordinator (even if
there is a coordinator for the program).

Any suggestions? I've posted the queries I'm working from (I tried to trim
them, but ended up posting the whole thing so I don't cut anything important):

Here's the subquery:
SELECT Event.EventVolunteerID, Organizations.OrganizationName,
Volunteering.VolunteerName, Organizations.OrganizationState,
Frequency.Frequency, Volunteering.VolunteerREAD, Frequency.FrequencyFactor,
Volunteering.VolunteerEndDate, Volunteering.VolunteerStartDate
FROM (Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes))
ORDER BY Volunteering.VolunteerDay DESC;

Here's the query:
SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
ORDER BY Contacts.LastName, Contacts.LastName;

I'd appreciate any suggestions on how I can end up with OrganizationName,
Volunteering.VolunteerName with no one in them as a Coordinator.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

My head has exploded! And sadly, the gray matter splatter didn't have much
diameter to it.

This is correct: Am I correct in the guess that you have multiple contacts
working on an event and one of them is the coordinator, but you are trying to
identify an event that doesn't have one of the contacts flagged as the
coordinator?

I still couldn't identify programs without coordinators.

I do have 2 queries-
This one gives me programs that have coordinators:
SELECT Event.Coordinator, Volunteering.VolunteerName,
Volunteering.VolunteeringID, Volunteering.VolunteerOngoing
FROM Event INNER JOIN Volunteering ON Event.VolunteeringID =
Volunteering.VolunteeringID
WHERE (((Event.Coordinator)=Yes) AND ((Volunteering.VolunteerOngoing)=Yes));

This one gives me distinct programs:
SELECT DISTINCT Volunteering.VolunteerName, Volunteering.VolunteerOngoing
FROM Volunteering INNER JOIN Event ON Volunteering.VolunteeringID =
Event.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes));

So my first query brings back my 2 programs that have coordinators, while
the second query brings back my 3 distinct programs (coordinators or no). So
it seems easy to tell what program is missing a coordinator. And yet an
unmatched query brings back nothing (grayed out). Argh!

Any suggestions? Thanks for your time.


John Spencer (MVP) said:
AHHH, methinks a light just went on. Now if I can keep from extinguishing it.

Am I correct in the guess that you have multiple contacts working on an event
and one of them is the coordinator, but you are trying to identify an event that
doesn't have one of the contacts flagged as the coordinator?

If so,

Make the simplest query you can that show events with coordinator. Save that as
query one

Now, use that along with the events table in a unmatched query (there is a
wizard for this).

GENERICALLY something like

SELECT EventContacts.EventID
FROM EventContacts
WHERE EventContacts.Coordinator = True

Which would give you all the events with a coordinator. Save that as qHaveCoordinator.

SELECT *
FROM SomeTable LEFT JOIN qHaveCoordinator
ON SomeTable.EventID = qHaveCoordinator.EventID
WHERE qHaveCoordinator.EventID is NULL

Hope this is a bit clearer. IF not, will try again later. Gotta go now.
John,

Event.Coordinator is a yes/no and with only Event.Coordinator = True I get
back a list of all programs that have a Coordinator.

Contacts.LastName is Null brings back nothing, grayed out results. This is
also correct because I'm not looking for where a program doesn't have an
associated Contact.

My concern is that I have a program out there with Contacts assigned to it,
but none of the Contacts have been assigned as the Coordinator. So I'm
trying to run a query that shows either ALL of the programs- those with a
designated Coordinator and those without. Or the programs that have assigned
Contacts, but no assigned Coordinator.

Any suggestions?

John Spencer (MVP) said:
Ok, since I'm not sure of your fields etc.

Is Event.Coordinator a yes/no field? If it is (it may be represented by a
check box) when you view it. Do you check it to indicate that the person -
member name - is the coordinator?

If so, then see if you get any records when you use ONLY the criteria
Event.Coordinator = True

Now, try ONLY
Contacts.LastName is Null

If those both give you the expected results then try both criteria with AND
between them. The other possibility is that Last Name is not null but an empty string.

Try
Contacts.LastName <> ""
as criteria if Contacts.LastName is Null doesn't give you the expected results.


Stephanie wrote:

John, Thanks for the reply. The name of the Coordinator is Contacts.LastName
and Contacts.FirstName or Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name]. What is "true" looking for?

I tried the query using Coordinator as True and Member Name as Is Null, but
the query returned nothing although I do have a program without a
Coordinator. (I threw in the "Past" field in case I had a Coordinator in the
Past) Here's what I have:

SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE (((Contacts.LastName) Is Null) AND ((Event.Past)=No) AND
((Event.Coordinator)=True))
ORDER BY Contacts.LastName;

The queries that I've played with return every variation except the one I
need! Any suggestion? Thanks.

:

I would say that you need to check if the Coordinator field is True and if
whatever field you store the name of the coordinator is null.

As a guess, is it VolunteerName that is the coordinator?

SELECT Event.ContactID,
Nz([NickName],[FirstName]) & " " & [LastName] AS [Member Name],
Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator,
[Program Opportunities subqry].OrganizationName,
[Program Opportunities subqry].VolunteerName,
[Program Opportunities subqry].Frequency,
[Program Opportunities subqry].OrganizationState,
[Program Opportunities subqry].VolunteerREAD,
[Program Opportunities subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
WHERE Event.Coordinator = True and VolunteerName is Null
ORDER BY Contacts.LastName, Contacts.LastName;

Stephanie wrote:

Hi. I have a yes/no field for Coordinator. Each program has one
coordinator. I'm trying to figure out a query to find programs that have no
coordinator, but because I used yes/no, I can't seem to get what I want. If
I use "no" in the query, I get EVERYONE who isn't a coordinator (even if
there is a coordinator for the program).

Any suggestions? I've posted the queries I'm working from (I tried to trim
them, but ended up posting the whole thing so I don't cut anything important):

Here's the subquery:
SELECT Event.EventVolunteerID, Organizations.OrganizationName,
Volunteering.VolunteerName, Organizations.OrganizationState,
Frequency.Frequency, Volunteering.VolunteerREAD, Frequency.FrequencyFactor,
Volunteering.VolunteerEndDate, Volunteering.VolunteerStartDate
FROM (Frequency INNER JOIN (Volunteering INNER JOIN Event ON
Volunteering.VolunteeringID = Event.VolunteeringID) ON Frequency.FrequencyID
= Volunteering.FrequencyID) INNER JOIN (Organizations INNER JOIN
EventSponsors ON Organizations.OrganizationID = EventSponsors.OrganizationID)
ON Volunteering.VolunteeringID = EventSponsors.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes))
ORDER BY Volunteering.VolunteerDay DESC;

Here's the query:
SELECT Event.ContactID, Nz([NickName],[FirstName]) & " " & [LastName] AS
[Member Name], Contacts.LastName, Animals.AnimalName, Event.Past,
Event.Coordinator, [Program Opportunities subqry].OrganizationName, [Program
Opportunities subqry].VolunteerName, [Program Opportunities
subqry].Frequency, [Program Opportunities subqry].OrganizationState, [Program
Opportunities subqry].VolunteerREAD, [Program Opportunities
subqry].FrequencyFactor
FROM [Program Opportunities subqry] INNER JOIN ((Event LEFT JOIN Animals ON
Event.AnimalsID = Animals.AnimalsID) INNER JOIN Contacts ON Event.ContactID =
Contacts.ContactID) ON [Program Opportunities subqry].EventVolunteerID =
Event.EventVolunteerID
ORDER BY Contacts.LastName, Contacts.LastName;

I'd appreciate any suggestions on how I can end up with OrganizationName,
Volunteering.VolunteerName with no one in them as a Coordinator.
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

Calendar reports 9

Top