Hi
I have attached a db.jpg ( a screen shot) of my MS Access Database
here we have 3 tables S_T,SI_T and SO_T
S_T is the main Tables
S_T primary key S_Id is used as foriegn key in SI_T as SI_Section_Id  and PRimary key of SI_T table is SI_Id
also
the Primary key of SI_T is used as Foriegn key in Table SO_T as SO_Inspecs_id ,the Primary key of SO_T is SO_ID.
i need a query that joins the three tables and displays 1 record of Each Record of Main Table S_T
e.g if S_t has 1 record and it is used as PK at 2 records in SI_T then
the result should only displays one record
we can say that multiple instances of Foriiegn keys should be ignored and only one record shoud be displayed
Regards


____________________________
@#@#@

use an SQL statement, but first you have to add a data environment to the project

Hi
No screenshot appears, but you need something like the following:

SELECT *
FROM
S_T
LEFT OUTER JOIN
SI_T ON (S_T.S_Id = SI_T.SI_Section_Id)
LEFT OUTER JOIN
SO_T ON (SO_T.SO_Id = SO_T.SO_Inspecs_Id)

Please note that you have described your PKs in a very confused manner, so the ON () fields may not be entirely correct.
This resultset will contain..
ALL records from S_T, and only those records from SI_T that match on SI_T.SI_Section_ID, it will also return ANY matching records from SO_T.

Hope this helps
Kieron

____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

or you can  declar  string  variable in general  like this
and after you add the  fileds to Datareport

write this code

'in generaql Dim SQL as string 'in command 1 or Printcmd sql= SQL = "select tb_product.*,tb_category.*,tb_factory.* from tb_product,tb_factory,tb_category where tb_product.category=tb_category.number and tb_product.factory=tb_factory.number " Dim cn As ADODB.Connection Dim Rs As ADODB.Recordset Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & Folder & "db1.mdb;" & "Jet OLEDBatabase Password=" & PassWord Set Rs = New ADODB.Recordset Set Rs = cn.Execute(SQL) Set DataReport1.DataSource = Rs DataReport1.Show Set cn = Nothing Set Rs = Nothing

[Edited by Jihadalariqi on 18-12-2007 at 10:47 PM GMT]

____________________________
vbboy