SUBQUERIES in the FROM clause with Access query

[A.Q]

Hi all,

I have this long query that extract datas from 12 tables, in that main query
there are 8 subqueries, it work fine with pass-thru, but now this query need
to do as access query. I need to have all info from table1, and only info
from table2 which matched table1, only info from table3 which matched
table2....

Below is example of 4 tables only since I need to understand the way to
right subqueries with access on a multi-outer join,
Relationships:
Table1--->Table2--->Table3
|
v
Table4

Table1 fields: ID(primary key), code,Tb1_field2, Tb1_field3, Tb1_field4... etc
Table2 fields: ID(foreign key), seq_id, TB2_ID(FK to table 3), flag,
tb2_field1, tb2_field2 ...etc
table3 fields: TB3_ID(PK), tb3_field1,tb3_field2...etc
Table4 fileds: code(pk), code_desc

my pass-thru like this:

SELECT DISTINCT TABLE1.ID,TABLE1.Tb1_field2, TABLE1.Tb1_field3,
TABLE1.Tb1_field4, MYSUB.tb3_field1, MYSUB.tb3_field2, table4.code_desc
FROM TABLE1, table4,
(SELECT TABLE2.ID, TABLE2.code, TABLE2.role, TABLE2.flag,
TABLE3.tb3_field1, TABLE3.tb3_field2 WHERE TABLE2.role ='2' AND
TABLE2.flag='T' and TABLE3.TB3_ID = TABLE2.TB2_ID) MYSUB
WHERE TABLE1.ID = MYSUB.ID(+) and
table1.code = table4.code(+)

Can anyone help me how to put this in ACCESS query?

Thanks in advance!
AQ

 

[Tom Ellison]

Dear AQ:

I'd like to start with this:

SELECT T1.ID, T1.Tb1_field2, T1.Tb1_field3,
T1.Tb1_field4, M.tb3_field1, M.tb3_field2, T4.code_desc
FROM TABLE1 T1, table4 T4,
(SELECT T2.ID, T2.code, T2.role, T2.flag,
T3.tb3_field1, T3.tb3_field2
FROM ??????
WHERE T2.role ='2'
AND T2.flag = 'T'
AND T3.TB3_ID = T2.TB2_ID)
M
WHERE T1.ID = M.ID(+)
AND T1.code = T4.code(+)

Above, I have studied your code and reformatted it for my personal reading
preferences. I have also added aliasing which improves readability for me.

Here's what I see. I have added FROM ?????? at a point in the subquery
where a FROM clause is mandatory. It seems to be missing. Also, what's the
(+) business. To what database was this a working "pass-thru"? Perhaps
that explains the syntax differences, but I've never seen a database where a
FROM clause can be omitted without causing serious problems. And, again,
I'm not familiar with the (+) thing. Neither is Access Jet I expect.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

 

[A.Q]

Hi Tom,
Thanks!!!
I changed the table names in the posted but i must mistake not completely
change all the table names with the original working sql. instead id
FAD_STATUS_HISTORY is Property_History. And missed the P for Person table.
Anyway. I will try the way you insert the subqueries in join statement and
learn more about it.

Good news that our group leader just decided not to translate that old
oracle sql to access sql cuz we spent too much time on it and none of us got
the sql work!. Phewwwww...

I will take more closer look to this matter when I have time. Thanks so
much for your help. I learn alot with access sql after this. And I'm really
appreciated your time and help!

Thank you
AQ

 

[Tom Ellison]

Dear AQ:

Next time your group leader wants you to work in Access or SQL Server, why
not give me a buzz. After what I learned from you, I'm feeling frisky about
doing some of this.

Tom Ellison