sql join via odbc client access examples

[mikej28621]

I am trying to build a multi table join sql statement using Excel VBA thru client access for an AS400 DB2 database.

I do single tables all the time. I would love to move on to multi table queries. Here is what I have so far. Any ideas or examples would be helpful

sqlstr = "SELECT T1.PSSVCTYP, T1.PSSVCID, T2.SVACCT, T2.SVBRCH, T3.AMNAME, T1.PSPCOD, T4.PMDESC, T4.PMTHDT, T4.PMCYCLES, T1.PSCYCLES"
sqlstr = sqlstr & " FROM " & CurrLib & ".SVCWIL/PROSVCID, SVCWIL/SRVMAST99, SVCWIL/ACTMAST, SVCWIL/PROMSTR"
sqlstr = sqlstr & " WHERE T1.PSSVCID = T2.SVCID AND T2.SVACCT = T3.AMACCT AND T2.SVBRCH = T3.AMBRCH AND T1.PSPCOD = T4.PMPCOD"
sqlstr = sqlstr & " Order by T1.PSSVCID"

Thanks

 

[PHV]

You haven't assigned the T1,T2,... aliases.
Perhaps something like this ?
sqlstr = sqlstr & " FROM " & CurrLib & ".SVCWIL/PROSVCID AS T1," & CurrLib & ".SVCWIL/SRVMAST99 AS T2," ....

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244