SQL Query sintax to get get multiple records in a group

[BuckyBaby]

Hello - I think I am close but can't quite get it!

I have a database that tracks corporate board members, so each record line includes an organization (Org)name and the last name of the member (LastName)..amoung other junk.

What I want to write is a statement that says "find the record lines that have a lastname of "smith" and a lastname of "jones" where the organization is the same (ie.. they both served together in the same Org). This is what I've done before to make this kind of think work..

SELECT tblMain.LastName, tblMain.Org
FROM tblMain
WHERE Org IN (Select Org from tblMain WHERE tblMain.LastName="Smith&quot
AND
Org IN (Select Org from tblMain WHERE tblMain.LastName="Jones&quot

when I run it it seems to ignore the name qualifier. I suspect this has something to do with the fact that what's in my Org field is comming from a dropdown listbox - could that be messing it up, since the value isn't actually typed in? If so, is there a way around this to make it work?

Thanks much,
Bucky [sig][/sig]

 

[RoyVidar]

Hi!

It seems to me that your statement demands the presence of both "Jones" AND "Smith" in one record. Could you try changing the "AND"-clause with an "OR".

Roy-Vidar [sig][/sig]

 

[BuckyBaby]

Well...I really don't want an OR because I don't want to find all the records that have Jones and all the records that have Smith, but the records where (for example...)

record #1 Org = Democrat LastName = Gore
record #2 Org = Democrat LastName = Clinton
record #3 Org = Democrat LastName = Lieberman
record #4 Org = Republican LastName = Gore

if in my query I make "LastName = Gore" in the first statement and "LastName = Lieberman" in the second statement, I want my query to answer the data in records 1 & 3 in the view. (So I'm asking in plain english "in what (same)organization have both Gore & Lieberman served?&quot

ugh - this is hard for me to explain!
Thanks!
BuckyBaby
[sig][/sig]

 

[DMac]

Try this:

Select a.Org, a.LastName, b.LastName
from tblMain a, tblMain b
WHERE a.LastName = "Macrae"
and b.LastName = "Test"
and a.Org = b.Org

Derek Macrae [sig][/sig]

 

[BuckyBaby]

THANKS DMAC - YOU Da Man! It works

BuckyBaby [sig][/sig]