iHaveUsedSelectNQueryStatementButStillHowCanIJoinMoreThan1Tables

[Guest_imported]

Yes, i've already used select statement to specify what fields of the different tables to be printed.. but still i don't know how could i slect these certain field from another 3 tables... here's the thing....


main1 tbl main2 tbl main3 tbl guardian tbl test tbl spouse tbl
studno studno studno studno studno studno
field1 field3 field4 gname test1 sname
field2 field4 field5 gadd test2 children

temp tbl
studo
field1
field2
field3
field4
field5

form1.Text1.Text =====>>>>> current student number (studno)

let's consider the information above....
let's us just say that i have join the fields from main1, main2, and main3 tables by using this statement

db.Execute "delete * from temp"
db.Execute "insert into temp select main1.studno, field1,field2,field3,field4, field5 from main1, main2, main3 where main1.studno = " & Val(form1.Text1.Text) & " and main2.studno = " & Val(form1.Text1.Text) & " main3.studno = " & val(form1.Text1.Text)

so now my question is... since that not all students have taken tests, have a guardian, and have a spouse.... how should i include the fields of the guardian, spouse, and test in the data report, specifying which current student to print.....

i know that my problem my be a "lame" one... but i'm just a beginner, hope u could help me... thanks alot!

 

[TNN]

Hi Jay,
It sounds like you need to use the SQL INNER JOIN operation and nest the join statements in order to pull a recordset from 3 different tables.
Look up the INNER JOIN in your sql reference. If you have microsoft access use help and type in "MICROSOFT SQL RESERVED WORDS" and then click on the reserved word JOIN. You should find there the syntax to nest JOIN statements.

Hope that helps.

TNN, Tom
TNPAYROLL@AOL.COM [sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]