Access - Tricky SQL statement

Question

I have an Access database with contacts, phone numbers, fax, mobile. Example:

Table: Numbers

Name       Number      Type
George     555555      Phone
George     656565      Phone
George     323232      Fax
Michael    656565      Phone
John       323232      Fax
Steve      234345      Mobile

I want to select the person who has phone number 656565 AND fax number 323232, namely here only George and not Michael or John. (Name is a foreign key if that helps) What is the SQL statement?

Answer 1

One possibility is set out below, but any answer will, I think, depend on [Name] being a unique key in whatever table it is from:

SELECT [Name] FROM Numbers 
WHERE Type = 'Phone' 
AND [Number] = '656565'
AND [Name] IN     
    (SELECT [Name] FROM Numbers 
     WHERE Type = 'Fax' 
     AND [Number] = '323232')

I have assumed that Number is a text field because phone numbers generally are stored as text.

Answer 2

You may want to look up the concept of correlated subqueries.