How to retrieve data on the form which has fields from two different tables.

  • Thread starter Thread starter ket_shah
  • Start date Start date
K

ket_shah

How to retrieve data on the form which has fields from two differen
tables.

Table1
------
code1
code2
code3

Table2
------
code1
test2
test3

column code1 is available between both the tables.

A form has a find button , text box and a tab control with 2 pages.

page 1 has

code2
test2

page 2 has

code3
test3

Enter value (code1) in text box and click on the find button

Want to get data on both the pages of tab control


I was able to get data from one table.

mstrSQL = "SELECT * FROM Table1 Where code1 = " text box "

.RecordSource = mstrSQL


How can I do that ? Any help is appreciated.

Thanks in advance
 
Set the record source of the form to

SELECT Table1.code2, Table1.code3, Table2.test2 Table2.test3 FROM Table1
INNER JOIN Table2 ON Table1.code1 = Table2.code1 Where Table1.code1 = "Criteria"

Pavel
 
Table1
------
code1
code2
code3

Table2
------
code1
test2
test3

SELECT Table1.code2, Table1.code3, Table2.test2 Table2.test3 FROM
Table1 INNER JOIN Table2 ON Table1.code1 = Table2.code1 Where
Table1.code1 = "Criteria"

What if I want to join more than two tables.

Table3
------
code1
test22
test22

It is possible ? Please advise.
 
Table1
------
code1
code2
code3

Table2
------
code1
test2
test3

SELECT Table1.code2, Table1.code3, Table2.test2 Table2.test3 FRO
Table1 INNER JOIN Table2 ON Table1.code1 = Table2.code1 Wher
Table1.code1 = "Criteria"

What if I want to join more than two tables.

Table3
------
code1
test22
test22

It is possible ? Please advise
 
SELECT Table1.code2, Table1.code3, Table2.test2 Table2.test3,
Table3.test22 FROM
(Table1 INNER JOIN Table2 ON Table1.code1 = Table2.code1) INNER JOIN
Table3 ON Table1.code1 = Table3.code1 Where Table1.code1 = "Criteria"

If it fails (I always get confused with parenthesis), just build it in
the query builder. It will make all the right SQL for you.

Pavel
 
Back
Top