Help! I'm having trouble with multiple table joins

[juniormint]

Hello -



I have to join 13 tables; 3 are left joins, one is right and all others are inner. I tried to do this in the query designer because Access doesn't like my joins from a Sql Server query. Even though Access creates the joins itself, it still gives an error - Join Not Supported.

This is exasperating! If it doesn't know what it wants, how should I know? I don't know what else to try.

Does anyone have an example of a multiple table query with multiple join types?

Any help will be greatly appreciated. Thanks.

[RayMilhon]

can you post the SQL from your Query here? it'll be easier to help

[June7]

I have some with INNER and RIGHT, INNER and INNER, LEFT and RIGHT, RIGHT and RIGHT. Didn't find any INNER and LEFT but I do have at least one with all three.
SELECT Submit.LabNum
FROM ConstructionSampleInfo INNER JOIN (0SoilsAggPrep RIGHT JOIN
(SUBMIT LEFT JOIN 105 ON SUBMIT.LabNum = [105].LabNum)
ON [0SoilsAggPrep].LabNum = SUBMIT.LabNum)
ON ConstructionSampleInfo.LabNum = SUBMIT.LabNum
WHERE (((Submit.MaterialType) = "Soils & Aggregate"))
ORDER BY Submit.LabNum;

Might have to do some intermediate queries.

If you want to provide db for analysis, follow instructions at bottom of my post.

[juniormint]

can you post the SQL from your Query here? it'll be easier to help

Thanks for your response. Below is pseudo code. I can't post the actual code. Any help is appreciated!

SELECT Linked_tbl1.col5, Linked_tbl2.col4, Linked_tbl3.col3, Linked_tbl3.col4, Linked_tbl3.col5, Linked_tbl3.col6,
Linked_tbl4.col2, Linked_tbl5.col8, Linked_tbl5.col9, Linked_tbl6.col2, Linked_tbl7.col3, Linked_tbl3.col8, Linked_tbl3.col9,
Linked_tbl7.col10, Linked_tbl8.col5, Linked_tbl8.col6, Linked_tbl9, Linked_tbl3.col3, CurrDB_tbl10.col4, Linked_tbl11.col7,
CurrDB_tbl11.col6, CurrDB_tbl11.col8, Linked_tbl6.col8, CurrDB_tbl11.col2, CurrDB_tbl10
RIGHT JOIN ((Linked_tbl11
LEFT JOIN ((((Linked_tbl8
LEFT JOIN ((Linked_tbl5
INNER JOIN (Linked_tbl2
LEFT JOIN ((Linked_tbl3
INNER JOIN Linked_tbl6 ON
Linked_tbl3.Key = Linked_tbl6.Key)
INNER JOIN Linked_tbl4 RMTRX ON
Linked_tbl6.Indexkey=Linked_tbl4.Indexkey) ON
Linked_tbl2.Key=Linked_tbl3.Key) ON
Linked_tbl5.Key=Linked_tbl3.Key)
INNER JOIN Linked_tb19 ON
Linked_tbl3.Key=Linked_tbl9.Key) ON
Linked_tbl8.Key=Linked_tbl3.Key)
INNER JOIN Linked_tbl7 ON
Linked_tbl3.Key=Linked_tbl7.Key)
INNER JOIN Linked_tbl1 ON
Linked_tbl3.Key=Linked_tbl1.Key)
INNER JOIN Linked_tbl7 ON
Lined_tbl3.Key=Linked_tbl7.Key) ON
Linked_tbl11=Linked_tbl3)
LEFT JOIN CurrDB_tbl11 ON
Linked_tbl6.CommonCol= CurrDB_tbl11.CommnCol) ON
CSTR(CurrDB_tbl10.NumericCol)=Linked_tbl3.TextCol
WHERE (((Linked_tbl3.Date) Between #1/2/2010# And #7/1/2012#) AND
((Linked_tbl4.Col5)=1));

[juniormint]

Thanks for responding. Do you know if there a specific order the three different joins are supposed to have? Please see my pseudo-code above. I can't upload the database due to confidentiality issues. By the way, the pseudo-code above was produced by Access and Access does not like its own code.

[June7]

You have relationships set up? Access query designer will generate the join syntax based on established relationships. You can change the jointype in query if necessary. I suspect all the INNER joins are causing issue. I would have to test query with tables. Don't need real data, just dummy records. If you can't provide, can't help.

[recyan]

Apart from June's suggestion,
Is there a copy-paste error in the SQL you have posted or am I missing something?

 
SELECT 
	Linked_tbl1.col5, Linked_tbl2.col4, 	Linked_tbl3.col3, Linked_tbl3.col4, 	Linked_tbl3.col5, 	Linked_tbl3.col6,	Linked_tbl4.col2, 	Linked_tbl5.col8, 	Linked_tbl5.col9, 
	Linked_tbl6.col2, 	Linked_tbl7.col3, 	Linked_tbl3.col8, 	Linked_tbl3.col9,	Linked_tbl7.col10, 	Linked_tbl8.col5, 	Linked_tbl8.col6, 
	Linked_tbl9, 
	Linked_tbl3.col3, 	CurrDB_tbl10.col4, 	Linked_tbl11.col7,	CurrDB_tbl11.col6, 	CurrDB_tbl11.col8, 	Linked_tbl6.col8, 	CurrDB_tbl11.col2, 
	CurrDB_tbl10
 RIGHT JOIN
.....

Also is there a FROM ... missing ?

Thanks

[juniormint]

Apart from June's suggestion,
Is there a copy-paste error in the SQL you have posted or am I missing something?

 
SELECT 
    Linked_tbl1.col5, Linked_tbl2.col4,     Linked_tbl3.col3, Linked_tbl3.col4,     Linked_tbl3.col5,     Linked_tbl3.col6,    Linked_tbl4.col2,     Linked_tbl5.col8,     Linked_tbl5.col9, 
    Linked_tbl6.col2,     Linked_tbl7.col3,     Linked_tbl3.col8,     Linked_tbl3.col9,    Linked_tbl7.col10,     Linked_tbl8.col5,     Linked_tbl8.col6, 
    Linked_tbl9, 
    Linked_tbl3.col3,     CurrDB_tbl10.col4,     Linked_tbl11.col7,    CurrDB_tbl11.col6,     CurrDB_tbl11.col8,     Linked_tbl6.col8,     CurrDB_tbl11.col2, 
    CurrDB_tbl10
RIGHT JOIN
.....

Also is there a FROM ... missing ?

Thanks

Yeah -- I did forget to copy the From, although it is in the original. I figured out my problem. If you notice in my code, I was trying to edit the Access code; in other words, write it like Access would. I finally started from scratch and simply used for example, FROM tbl3 and put each join in like I would in sql server, but each time I did, I put a parentheses at the end of that particular join and I when back to the tbl3 part and added a parenthesis there until, of course, the last join, which simply has a semicolon. Thanks for everyone who responded!

[juniormint]

Since this was an existing DB, I had thought the relationships were already set up. Turned out they weren't. I found that out after I had written all the code out by hand, though . . . sigh

Thanks for the help!

[juniormint]

Where do I mark the June7 solution as the answer? I'm not seeing a button . . .