Problem with SQL from Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]
This is the error I get:
The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128
Incorrect syntax near the keyword 'AS'
Any ideas?
Thanks
FROM [SELECT product.product...
...
((product.display)="y"))]. AS ...
Should be:
FROM (SELECT product.product...
...
((product.display)="y"))). AS ...
Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
for how you enclose a derived table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0409271247.5a340b82@.posting.google.c om...
> SELECT DISTINCT product.product_id AS Expr1
> FROM [SELECT product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> dbo_ssc_product.product_id = product.product_id
> GROUP BY product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> HAVING (((product.item_name) Like "*coaster*") AND
> ((product.display)="y"))]. AS [Alias]
> This is the error I get:
> The identifier that starts with 'SELECT product.product_id...' is too
> long. Maximum lenght is 128
> Incorrect syntax near the keyword 'AS'
>
> Any ideas?
> Thanks
|||Hi Tibor,
thanks for your help, you're right about the parenthesis and I also
found that you need to also remove the dot.
the code below ended up working:
FROM (SELECT product.product...
...
((product.display)="y"))) AS ...
without the dot.
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OCMZv4SpEHA.536@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> FROM [SELECT product.product...
> ...
> ((product.display)="y"))]. AS ...
> Should be:
> FROM (SELECT product.product...
> ...
> ((product.display)="y"))). AS ...
> Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
> for how you enclose a derived table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Allan" <proflicker@.hotmail.com> wrote in message
> news:7b5b0602.0409271247.5a340b82@.posting.google.c om...
sql