Access requires AS in alias table names.



From: "Julian Fraser" <julian@f...> on 06/15/2002 01:18 AM GMT

Please respond to "ASP Databases" <asp_databases@p...>

To:       "ASP Databases" <asp_databases@p...>
cc:

Subject:  [asp_databases] SQL Statement trouble with MS Access


Can anyone tell me how to get this statement to work with access? I use
this statement syntax all the time with SQL Server, but access throws a
fit when I feed it this.

strSQL = "SELECT p.productID, p.name, ptb.duration, ptb.durationperiod " &_
"FROM products p " &_
"INNER JOIN producttypes pt ON p.producttype = pt.producttype " &_
"LEFT OUTER JOIN producttimebased ptb ON ptb.productID = ptb.productID " &_
"WHERE pt.subscription = true " &_
"ORDER BY p.producttype, p.productID"

Thanks,
Jules.

Can anyone tell me how to get this statement to work with access? I use 
this statement syntax all the time with SQL Server, but access throws a 
fit when I feed it this.

strSQL = "SELECT p.productID, p.name, ptb.duration, ptb.durationperiod " &_
"FROM products p " &_
"INNER JOIN producttypes pt ON p.producttype = pt.producttype " &_
"LEFT OUTER JOIN producttimebased ptb ON ptb.productID = ptb.productID " &_
"WHERE pt.subscription = true " &_
"ORDER BY p.producttype, p.productID"

Thanks,
Jules.

Jules,

Please try this: 

strSQL = "SELECT products.productID, products.name, 
producttimebased.duration, producttimebased.durationperiod " &_
" FROM products " &_
" INNER JOIN producttypes ON products.producttype = 
productTypes.producttype " &_
" LEFT OUTER JOIN producttimebased ON producttimebased.productID = " 
&_ "producttimebased.productID WHERE ProductTypes.subscription = true " &_
" ORDER BY Products.producttype, Products.productID"

I hope this helps you.

Jimi