Susan Harkins
ssharkins at bellsouth.net
Sun Apr 24 18:00:50 CDT 2005
Yes SELECT Species.SpecID FROM Species WHERE NOT EXISTS (SELECT CBCData.CountID From CBCData WHERE CBCData.CountID <> 57) ORDER BY Species.SpecID Returns no records SELECT Species.SpecID FROM Species WHERE EXISTS (SELECT CBCData.CountID From CBCData WHERE CBCData.CountID <> 57) ORDER BY Species.SpecID Returns them all. Using this syntamx, there's no relationship between the two SELECT statements. I've tried adding a join to the mix -- in the sub and the main -- but haven't been successful yet. Susan H. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Lonnie Johnson Sent: Sunday, April 24, 2005 6:43 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] SQL challenge In your sub query are you saying... In (Select countID From SomeTable Where countID <> 57) Susan Harkins <ssharkins at bellsouth.net> wrote: 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? -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com May God bless you beyond your imagination! Lonnie Johnson ProDev, Professional Development of MS Access Databases Visit me at ==> http://www.prodev.us __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com