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.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <julian@f...>
Subject: [access_asp] SQL Statement Trouble in 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"

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There seem to be a few problems:

a) In Access you need to perform a JOIN on a table/view type entity, hence a
need for parenthesis in your JOIN clauses.

b) You need to use the AS keyword when aliasing a table.

c) Name is also not a good name for a fieldname - use something more
descriptive that isn't reserved, such as ProductName.

d) In your OUTER JOIN clause you have the following:
        ON ptb.productID = ptb.productID
    you are joining the wrong tables together

As a suggestion, build the query inside the Access QBE (Queries, New Query
in Design View, SQL View), and then cut-n-paste the resulting SQL into your
ASP page.

SELECT
    p.ProductID,
    p.ProductName,
    ptb.Duration,
    ptb.DurationPeriod
FROM
    Products AS p
INNER JOIN
    (
        ProductTypes AS pt
    LEFT OUTER JOIN
        ProductTimeBased AS ptb
    ON
        pt.ProductID = ptb.ProductID
    )
ON
    p.ProductType = pt.ProductType
WHERE
    pt.Subscription = True
ORDER BY
    p.ProductType,
    p.ProductID

Cheers
Ken