Yes/No or None

S

Stephanie

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.
 
S

Sam

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.
 
S

Stephanie

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...
 
S

Stephanie

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...
 
S

Stephanie

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...
 
S

Stephanie

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...
 
J

John Spencer (MVP)

I'm sorry to be confused, but which field tells you the program name or better
yet a unique identifier for the program in those two queries?

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?

:

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...
 
S

Stephanie

I admit, the structure is a bit complex but I tried to make the queries as
simple (least amount of tables) as possible.

Volunteering.VolunteeringID is unique. Volunteering.VolunteerName is the
name of the program (such as "Reading with Kids"). I see where you are
going, I think, so I fixed my queries to makes sure that they are both
bringing back VolunteeringID and VolunteerName, but when I did an unmatched
query, it only broght back the Matched records.

qry1: (only VolunteeringID that has associated Coordinator)
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));

qry2: (all programs with VolunteeringID)
SELECT DISTINCT Volunteering.VolunteerName, Volunteering.VolunteerOngoing,
Volunteering.VolunteeringID
FROM Volunteering INNER JOIN Event ON Volunteering.VolunteeringID =
Event.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes));

q1 without matching q2 (which actually brings back q1 results):
SELECT qry1.VolunteerName, qry1.VolunteeringID
FROM qry1 LEFT JOIN qry2 ON qry1.VolunteerName = qry2.VolunteerName;

Here's a bit about the table structure:

Volunteering
VolunteeringID
VolunteeringName
VolunteeringOngoing

Event
EventVolunteeringID
VolunteeringID
Coordinator

Sorry this has gotten so complicated! I appreciate your help.


John Spencer (MVP) said:
I'm sorry to be confused, but which field tells you the program name or better
yet a unique identifier for the program in those two queries?

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.

Stephanie wrote:

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?

:

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...
 
J

John Spencer

Which query returns the most records ? - qry2 is my guess
Which query returns the least records? - qry1 is my guess

I am still confused a bit, but perhaps the following will work for you.

I thought you wanted q2 that did not have a match in q1

SELECT qry2.VolunteerName, qry2.VolunteeringID
FROM qry2 LEFT JOIN qry1ON qry2.VolunteerName = qry1.VolunteerName
WHERE qry1.VolunteerName is Null

Stephanie said:
I admit, the structure is a bit complex but I tried to make the queries as
simple (least amount of tables) as possible.

Volunteering.VolunteeringID is unique. Volunteering.VolunteerName is the
name of the program (such as "Reading with Kids"). I see where you are
going, I think, so I fixed my queries to makes sure that they are both
bringing back VolunteeringID and VolunteerName, but when I did an
unmatched
query, it only broght back the Matched records.

qry1: (only VolunteeringID that has associated Coordinator)
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));

qry2: (all programs with VolunteeringID)
SELECT DISTINCT Volunteering.VolunteerName, Volunteering.VolunteerOngoing,
Volunteering.VolunteeringID
FROM Volunteering INNER JOIN Event ON Volunteering.VolunteeringID =
Event.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes));

q1 without matching q2 (which actually brings back q1 results):
SELECT qry1.VolunteerName, qry1.VolunteeringID
FROM qry1 LEFT JOIN qry2 ON qry1.VolunteerName = qry2.VolunteerName;

Here's a bit about the table structure:

Volunteering
VolunteeringID
VolunteeringName
VolunteeringOngoing

Event
EventVolunteeringID
VolunteeringID
Coordinator

Sorry this has gotten so complicated! I appreciate your help.


John Spencer (MVP) said:
I'm sorry to be confused, but which field tells you the program name or
better
yet a unique identifier for the program in those two queries?

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.

:

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.

Stephanie wrote:

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?

:

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...
 
S

Stephanie

Thanks, John.

Yes, qry 2 brings back all of my programs, even if there is no coordinator.
qry1 only brings back the programs that have coordinators, so that those
programs without coordinators are not returned.

I do want to know the difference between qry1 and qry2: the programs without
coordinators.

I don't know how you did it, because all VolunteerNames have values. BUT IT
WORKED! Thank you. You're much more talented than the Unmatched Query
Wizard ;-) I appreciate it!



John Spencer said:
Which query returns the most records ? - qry2 is my guess
Which query returns the least records? - qry1 is my guess

I am still confused a bit, but perhaps the following will work for you.

I thought you wanted q2 that did not have a match in q1

SELECT qry2.VolunteerName, qry2.VolunteeringID
FROM qry2 LEFT JOIN qry1ON qry2.VolunteerName = qry1.VolunteerName
WHERE qry1.VolunteerName is Null

Stephanie said:
I admit, the structure is a bit complex but I tried to make the queries as
simple (least amount of tables) as possible.

Volunteering.VolunteeringID is unique. Volunteering.VolunteerName is the
name of the program (such as "Reading with Kids"). I see where you are
going, I think, so I fixed my queries to makes sure that they are both
bringing back VolunteeringID and VolunteerName, but when I did an
unmatched
query, it only broght back the Matched records.

qry1: (only VolunteeringID that has associated Coordinator)
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));

qry2: (all programs with VolunteeringID)
SELECT DISTINCT Volunteering.VolunteerName, Volunteering.VolunteerOngoing,
Volunteering.VolunteeringID
FROM Volunteering INNER JOIN Event ON Volunteering.VolunteeringID =
Event.VolunteeringID
WHERE (((Volunteering.VolunteerOngoing)=Yes));

q1 without matching q2 (which actually brings back q1 results):
SELECT qry1.VolunteerName, qry1.VolunteeringID
FROM qry1 LEFT JOIN qry2 ON qry1.VolunteerName = qry2.VolunteerName;

Here's a bit about the table structure:

Volunteering
VolunteeringID
VolunteeringName
VolunteeringOngoing

Event
EventVolunteeringID
VolunteeringID
Coordinator

Sorry this has gotten so complicated! I appreciate your help.


John Spencer (MVP) said:
I'm sorry to be confused, but which field tells you the program name or
better
yet a unique identifier for the program in those two queries?


Stephanie wrote:

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.

:

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.

Stephanie wrote:

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?

:

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
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

IIf? 2
Calendar reports 19
yes/no or both Error 3071 2
2 similar tables, 1 query (?) 4

Top