2 queries same field

[2Stupid2ownAputer]

Hello all,
I have a slight problem I hope you may be able to help me with
I need to select a group of people where a persons surname appears in that
group.
This is the sql code I use.
MyGroup is my table. and smith is the surname

SELECT DISTINCT MyGroup1.SID, MyGroup1.ID AS MyID, MyGroup1.Title,
MyGroup1.Forename, MyGroup1.Surname, MyGroup1.DoD, MyGroup1.AoD,
MyGroup1.DoB, MyGroup1.YoB, MyGroup1.InfantsDWM, MyGroup1.Relationship,
MyGroupe1.Additional
FROM MyGroup INNER JOIN MyGroup AS MyGroup1 ON
INT([MyGroup].[Newmonid])=INT(MyGroup1.Newmonid)
WHERE ([MyGroup].[surname])=("smith")
ORDER BY MyGroup1.newmonid;

This works fine.

However I wish to extend this query so that I can do the same thing with two
different surnames in the same surname field at once so that I get groups
that always contain SMITH and BROWN
I also wish to modify my search to always include Ted SMITH and Charles
BROWN but first hurdle first ;-)
Example
Side A
John Black
Charles BROWN
Mike White
Ted SMITH
Amos Green

Side B
Ted Gray
Charles BROWN
Jack Doe
Joseph Smart
Ted SMITH

the Logic of the WHERE statement would be
WHERE ([MyGroup].[surname])=("smith") AND ([MyGroup].[surnname]) =("brown")

I suspect I may have to use another join

Any help appreciated
Thanks in advance

 

[Guest]

If the query is working with "smith" only then change your SQL from this ---
WHERE ([MyGroup].[surname])=("smith") AND ([MyGroup].[surnname]) =("brown")
to this ---
WHERE ([MyGroup].[surname])=("smith") Or ([MyGroup].[surnname]) =("brown")

 

[2Stupid2ownAputer]

Hi Karl thanks for your reply,
I tried that but then it returns all BROWNS and all SMITHS. whether they are
in the same group or not.

101.01 Ted Gray
101.02 Charles BROWN
101.03 Jack Doe
101.04 Joseph Smart
101.05 Ted SMITH

101 is the group ID and the decimal part is the person ID in that group.
I need the query to return the whole group if there is a Smith AND a Brown
in that group.
The problem seems to be is that both brown and smith are in the same field.
Once again thanks for your help.
Best Regards

 

[Guest]

101 is the group ID and the decimal part is the person ID in that group.
You need a separate field for GroupID and PersonID.