[AccessD] SQL challenge

Susan Harkins ssharkins at bellsouth.net
Sun Apr 24 16:33:11 CDT 2005


This is from a reader -- I've tried but can't come up with an answer. If I
put the WHERE countID <> 57 in the subquery, SQL ignores it (kind of) -- I
understand what's going on, but that's the gist of it. I can't figure out
how to exclude the countID = 57 records from the main query, and I've not
come up with a single SELECT that'll do it. 
 
I didn't include the mdb file -- if anyone's up for the challenge, I'll be
glad to send it. 
 
Susan H. 

  _____  




Susan,
 
I am sorry for "bothering" you, but I have been attempting to implement the
technique of yours that is detailed at this location:
http://www.devx.com/dbzone/Article/9570/1954?pf=true.
 
The results are not as expected. I will attach my database for you so you
can see for yourself, but below is an explanation of what I am attempting to
do.
I have two tables that are being used in this process. Species and CBCData.
The Species table contains a list of 423 species of birds that HAVE been
seen in Missouri. The CBCData table contains the species that have been
reported on various birding outings.
 
Near the very end of the CBCData table are two outings. These are identified
by the countID. One is 57 and the other is 62. For my purposes we are
working with the outing information from 57 only.
 
My goal is to generate a list of species that were NOT reported on the
countID=57 count by comparing the species that were reported
(CBCData.specID) with the TOTAL list of possible species in the
Species.specID field.
 
OK... First, there were 86 species reported in countID=57. Therefor a report
showing the species that were NOT reported should show (423 - 86 = 337) ...
337 species NOT reported.
 
HOWEVER, my query generates a completely different number (208)... I can't
discern WHY...  IT SHOULD REPORT 337....
 
Can you assist?
 
 



More information about the AccessD mailing list