SQL Statement

I want a query that returns a list of keys and names.

I have 2 tables, a primary (tblP) and an alternate (tblA).

tblP
Key [AutoNumber]
Name Text
xxx Many Fields (interger & text)

tblA
Key [AutoNumber]
Ptr->P Long 'This is a pointer to Key in tblP
Name Text

Assume the following data:
tblP
Key Name
001 Smith
002 Joynes
003 OldBirdman

tblA
Key Ptr->P Name
01 002 Joins
02 001 Smythe

Desired result from Query WHERE Name LIKE ""*o*"" - Name in either tblP or tblA
Key Name
002 Joynes
002 Joins
003 OldBirdman

I know that one solution is to remove Name from tblP, add all the names to tblA, and therefore have a simple query "SELECT tblA.[Ptr->P], tblA.Name FROM tblA WHERE tblA.Name LIKE ""*o*""" . Is there a way with "OUTER JOIN" or some other SQL Phrase to do this? I have tried to simplify this problem, and in doing so the simple solution of moving all Names to tblA presents itself. In the actual application, this is not a viable solution.

OldBirdman

re: SQL Statement

Hi,

Try This :


Regards
Veena

re: SQL Statement

I knew that there was an SQL keyword not accessable from Access Query Design. Thank you for both supplying the keyword and for the short, quick example.

Your example worked well within my application. Now I have to modify it to do exactly what I need done. Thanks again.

OldBirdman