problem regarding "Select Query"

[rishif2]

hello expertes
i have 2 tables , both having same table structure (i.e. same fields ) but having different Records. First Table for Male Candidate and another for Female Candidate.
both tables have 2 fields namely :- name and marks. Now i wanna write sql statement using adodb to perform the following:-

i wanna display all names(male and female) who secured at least 10 or higher.

Note:- i dont wanna use 2 recordsets for performing such a task . . .

[AndreRet]

What code do you have so far?

You will be making use of inner join statement if you have primary keys set to your tables fields.

[rishif2]

i opt the following to perform the task

con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path & "\db.mdb"
rec.Open "Here i want select query to retrieve data from 2 tables", con, 2, 3
While (rec.EOF = False)
Print rec.Fields(0); Space(10); rec.Fields(1)
Wend
con.Close
Set con = Nothing

i didnt set primary key on table and is it necessary to have primary keys to solve the problem ?
and i not much familiar with joins . . .

[Reverend Jim]

You want a UNION rather than a JOIN. Example:

SELECT * FROM Males   WHERE Mark > 50
 UNION
SELECT * FROM Females WHERE Mark > 50

will return one recordset

[AndreRet]

Thank you Jim, simple and easy as always. Should have put more thought into it. ;)