If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"negative" query
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]))); |
#2
|
|||
|
|||
"negative" query
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. "Stephanie" wrote: 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]))); |
#3
|
|||
|
|||
"negative" query
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! "KARL DEWEY" wrote: 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. "Stephanie" wrote: 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]))); |
#4
|
|||
|
|||
"negative" query
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. "Stephanie" wrote: 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! "KARL DEWEY" wrote: 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. "Stephanie" wrote: 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]))); |
#5
|
|||
|
|||
"negative" query
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! "KARL DEWEY" wrote: 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. "Stephanie" wrote: 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! "KARL DEWEY" wrote: 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. "Stephanie" wrote: 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]))); |
#6
|
|||
|
|||
"negative" query
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. "Stephanie" wrote: 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! "KARL DEWEY" wrote: 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. "Stephanie" wrote: 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! "KARL DEWEY" wrote: 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. "Stephanie" wrote: 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]))); |
Thread Tools | |
Display Modes | |
|
|