"negative" query

[Stephanie]

Hi. I'm trying to find all the locations that did NOT have incidents
reported. All I can seem to bring back is those locations that did report
incidents. I even branched out and tried a RIGHT join! I'd appreciate your
suggestions. Thanks!

SELECT DISTINCT tbOffices.Location
FROM qu_tbIncidents_Final_Ops_Completed_012 RIGHT JOIN tbOffices ON
qu_tbIncidents_Final_Ops_Completed_012.Location = tbOffices.Location
WHERE (((tbOffices.Location)=
[qu_tbIncidents_Final_Ops_Completed_012].[Location]) AND
((qu_tbIncidents_Final_Ops_Completed_012.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])));

 

[KARL DEWEY]

Try this --
SELECT DISTINCT tbOffices.Location
FROM tbOffices LEFT JOIN qu_tbIncidents_Final_Ops_Completed_012 ON
qu_tbIncidents_Final_Ops_Completed_012.Location = tbOffices.Location
WHERE ([qu_tbIncidents_Final_Ops_Completed_012].[Location] Is Null) AND
((qu_tbIncidents_Final_Ops_Completed_012.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate]));

 

[Stephanie]

Thanks. That didn't quite do it.

Here's the setup: tbIncidents_Final_Ops_Completed_012 contains all of the
pertinent incidents. This query also lists the locations where the incidents
did occurr.

tbOffices contains all of the locations that incidents COULD occur. So I'm
trying to determine which location (from tbOffices) does not have an
incident, i.e. does not appear in tbIncidents_Final_Ops_Completed_012.

Hope that explains it. Any suggestion? Thanks!

Try this --
SELECT DISTINCT tbOffices.Location
FROM tbOffices LEFT JOIN qu_tbIncidents_Final_Ops_Completed_012 ON
qu_tbIncidents_Final_Ops_Completed_012.Location = tbOffices.Location
WHERE ([qu_tbIncidents_Final_Ops_Completed_012].[Location] Is Null) AND
((qu_tbIncidents_Final_Ops_Completed_012.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate]));

--
Build a little, test a little.

Hi. I'm trying to find all the locations that did NOT have incidents
reported. All I can seem to bring back is those locations that did report
incidents. I even branched out and tried a RIGHT join! I'd appreciate your
suggestions. Thanks!

SELECT DISTINCT tbOffices.Location
FROM qu_tbIncidents_Final_Ops_Completed_012 RIGHT JOIN tbOffices ON
qu_tbIncidents_Final_Ops_Completed_012.Location = tbOffices.Location
WHERE (((tbOffices.Location)=
[qu_tbIncidents_Final_Ops_Completed_012].[Location]) AND
((qu_tbIncidents_Final_Ops_Completed_012.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])));

 

[Stephanie]

This query brought back nothing as the location of an incident isn't null- if
an incident occurred, there is a listed location (all of that information is
contained in tbIncidents_Final_Ops_Completed_012

I'm trying to compare that information against tbOffices, which lists ALL
locations and trying to determine which locations (tbOffices) did not have an
incident listed in tbIncidents_Final_Ops_Completed_012

Thanks for your time!

What did you not get that you were expecting? What did you get that you
should not have gotten?
Saying something is not quite right provides no explaination of what is wrong.
--
Build a little, test a little.

Thanks. That didn't quite do it.

Here's the setup: tbIncidents_Final_Ops_Completed_012 contains all of the
pertinent incidents. This query also lists the locations where the incidents
did occurr.

tbOffices contains all of the locations that incidents COULD occur. So I'm
trying to determine which location (from tbOffices) does not have an
incident, i.e. does not appear in tbIncidents_Final_Ops_Completed_012.

Hope that explains it. Any suggestion? Thanks!

Try this --
SELECT DISTINCT tbOffices.Location
FROM tbOffices LEFT JOIN qu_tbIncidents_Final_Ops_Completed_012 ON
qu_tbIncidents_Final_Ops_Completed_012.Location = tbOffices.Location
WHERE ([qu_tbIncidents_Final_Ops_Completed_012].[Location] Is Null) AND
((qu_tbIncidents_Final_Ops_Completed_012.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate]));

--
Build a little, test a little.


:

Hi. I'm trying to find all the locations that did NOT have incidents
reported. All I can seem to bring back is those locations that did report
incidents. I even branched out and tried a RIGHT join! I'd appreciate your
suggestions. Thanks!

SELECT DISTINCT tbOffices.Location
FROM qu_tbIncidents_Final_Ops_Completed_012 RIGHT JOIN tbOffices ON
qu_tbIncidents_Final_Ops_Completed_012.Location = tbOffices.Location
WHERE (((tbOffices.Location)=
[qu_tbIncidents_Final_Ops_Completed_012].[Location]) AND
((qu_tbIncidents_Final_Ops_Completed_012.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])));

 

[KARL DEWEY]

The query will return location for which has no incident location.
Try entering a new location in tbOffices and run the query. You should get
that new location as a result because there are no incidents assoicated with
it.

--
Build a little, test a little.

This query brought back nothing as the location of an incident isn't null- if
an incident occurred, there is a listed location (all of that information is
contained in tbIncidents_Final_Ops_Completed_012

I'm trying to compare that information against tbOffices, which lists ALL
locations and trying to determine which locations (tbOffices) did not have an
incident listed in tbIncidents_Final_Ops_Completed_012

Thanks for your time!

That didn't quite do it.

What did you not get that you were expecting? What did you get that you
should not have gotten?
Saying something is not quite right provides no explaination of what is wrong.
--
Build a little, test a little.

Thanks. That didn't quite do it.

Here's the setup: tbIncidents_Final_Ops_Completed_012 contains all of the
pertinent incidents. This query also lists the locations where the incidents
did occurr.

tbOffices contains all of the locations that incidents COULD occur. So I'm
trying to determine which location (from tbOffices) does not have an
incident, i.e. does not appear in tbIncidents_Final_Ops_Completed_012.

Hope that explains it. Any suggestion? Thanks!

:

Try this --
SELECT DISTINCT tbOffices.Location
FROM tbOffices LEFT JOIN qu_tbIncidents_Final_Ops_Completed_012 ON
qu_tbIncidents_Final_Ops_Completed_012.Location = tbOffices.Location
WHERE ([qu_tbIncidents_Final_Ops_Completed_012].[Location] Is Null) AND
((qu_tbIncidents_Final_Ops_Completed_012.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate]));

--
Build a little, test a little.


:

Hi. I'm trying to find all the locations that did NOT have incidents
reported. All I can seem to bring back is those locations that did report
incidents. I even branched out and tried a RIGHT join! I'd appreciate your
suggestions. Thanks!

SELECT DISTINCT tbOffices.Location
FROM qu_tbIncidents_Final_Ops_Completed_012 RIGHT JOIN tbOffices ON
qu_tbIncidents_Final_Ops_Completed_012.Location = tbOffices.Location
WHERE (((tbOffices.Location)=
[qu_tbIncidents_Final_Ops_Completed_012].[Location]) AND
((qu_tbIncidents_Final_Ops_Completed_012.[Discovery Date]) Between
CVDate([forms]![frquick]![StartDate]) And
CVDate([forms]![frquick]![EndDate])));