Access (Jet) SQL queries are basically the same as T-SQL, except that 'complex' queries won't work. Saying exactly what will and won't work isn't really possible.

In terms of syntax, you have encountered pretty much the only two differences between Jet SQL and T-SQL. When aliasing, Access requiresthe use of 'AS', whereas for T-SQL it is optional. Also, Access requires that you explicitly specify the join type, INNER, LEFT, or RIGHT. So just change

select a.col1,b.col1,a.col2,b.col2 from table1 a join table2 b on a.col1=b.col1

to

select a.col1,b.col1,a.col2,b.col2 from table1 AS a INNER join table2 AS b on a.col1=b.col1

and Access should be happy. Otherwise the syntax for joins is the same as in T-SQL.