If you have base table containing data plus key fields for a series of
related link tables, and you want to return a record set which contains all the records from the base table, and depending on if the key fields match, data from the link tables you can use the LEFT OUTER JOIN command which works fine for a single link table ie

Base Table AAAA
Link Table BBBB

SELECT * FROM AAAA LEFT OUTER JOIN BBBB ON AAAA.key=BBBB.key

But as soon as I increase this to say two (3) links tables it fails when using MS Access database from inside ADO Control.

Base Table AAAA
Link Table BBBB
Link Table CCCC
Link Table DDDD

SELECT *
FROM AAAA LEFT OUTER JOIN BBBB ON AAAA.key=BBBB.key
LEFT OUTER JOIN CCCC ON AAAA.key = CCCC.key
LEFT OUTER JOIN DDDD ON AAAA.key = DDDD.key

Can anyone advise how the above can be solved for tables in an MS Access (Office 2000) database.


Regards

Vipul Mehta


Vipul Mehta
Software Developer
Chenoa Information & Software Services Pvt Ltd

Hi,

   When you say "base" table, do you mean a junction table or a main table? And when you say "Link" table, do you mean a look up table? I think this would help based on the direction of the relationships.

   Are the "linked" tables looked up through a junction table like this:

tblEmployees <--- tblAssetAssignments ---> tblAssets

Or more are they just look ups like this:

tblEmployees ---> tblCities
tblEmployees ---> tblStates

Thanks,


mmcdonal

 Hi,
    I have 1 main table whose Primary Key becomes FK in 3 other tables. So I have written the following query that doesnt work. It give me error 'Syntax Error (missing operator) in query expression'. Plz help me out.

SELECT c.[Name], cp.[PolicyNumber], cp.[NameOfPolicy]
FROM
(Contact c LEFT OUTER JOIN [Contact_Policy] cp ON c.[ContactPK] = cp.[ContactFK])
LEFT OUTER JOIN [Contact_Family] cf ON c.[ContactPK] = cf.[ContackFK]
LEFT OUTER JOIN [Contact_Loan] cl ON c.[ContactPK] = cl.[ContactFK]


Regards,
 Vipul Mehta

Vipul Mehta
Software Developer
Chenoa Information &amp; Software Services Pvt Ltd

hi,
 i also had a close problem like your some weeks back. although i didn't get a solution to the three table JOINing. well i came to a concultion. i did some research on my own, i found out that Access support fractional part of SQL. I think the three table JOINing stuff will not work in ACCESS. but i didnt give. i use another method. try look deeply into the DISTINT, WHERE CLAUSE. you might fing you anwser there. i had to re-structured my table. you can use an ID to JOIN two table, and then use the WHERE CLAUSE to bring in the 3rd table using the 3rd table ID.

please you can send me a mail if you find the syntax for the 3-table JOIN.:)
SEE-YA

Use the graphical query editor.
place the three tables and connect the id's
Now select the connection line with a right-click and use option 2 or 3 to get the LEFT , RIGHT join arrow pointing in the direction you need.

Switch to the SQL mode to show the sql code


Jaime E. Maccou

Well after following the last point of advice, switching to the graphical editor, i did complete the task, but for more exacting detail, after it was complete and appeared to be functioning i reversed it back to sql view. Access can do it, but expects the joins to nest within each other.
so. . .
from tbl1 inner join (tbl2 inner join tbl3 on tbl3.fld1=tbl2.fld1) on tbl1.fld2 = tbl3.fld2

notice tbl3 (the meeting table with links to the other 2) is at the deepest level, linking up to item 2 at that level then up to table 1 on the top.