Error in expression ''

  • Thread starter Thread starter Ivan Debono
  • Start date Start date
I

Ivan Debono

Hi all,

I've got this query:

SELECT a.Item, b.Cost, b.Category
FROM [Table B] b
RIGHT OUTER JOIN [Table A] a
ON a.Item = b.Item
AND b.Cost = (SELECT MIN(Cost)
FROM [Table B] WHERE Item = b.Item)

But I get a Syntax error in expression '' error when I try to run it. This
works fine of SQL Server but not under Access 2003. Is there something wrong
with the statement, or Access can't handle this kind of statement? What's
the alternative?

Thanks,
Ivan
 
Hi Ivan,

Did you try

SELECT a.Item, b.Cost, b.Category
FROM [Table B] a
RIGHT OUTER JOIN [Table A] b
ON a.Item = b.Item
WHERE b.Cost = (SELECT MIN(c.Cost)
FROM [Table B] c WHERE c.Item = b.Item);


Good luck,

Gary Walter
 
Thanks for the push. This got half of it working. I had to do a UNION SELECT
for those records in TableA but not in TableB. It looks like it's working
now :)


Gary Walter said:
Hi Ivan,

Did you try

SELECT a.Item, b.Cost, b.Category
FROM [Table B] a
RIGHT OUTER JOIN [Table A] b
ON a.Item = b.Item
WHERE b.Cost = (SELECT MIN(c.Cost)
FROM [Table B] c WHERE c.Item = b.Item);


Good luck,

Gary Walter

I've got this query:

SELECT a.Item, b.Cost, b.Category
FROM [Table B] b
RIGHT OUTER JOIN [Table A] a
ON a.Item = b.Item
AND b.Cost = (SELECT MIN(Cost)
FROM [Table B] WHERE Item = b.Item)

But I get a Syntax error in expression '' error when I try to run it. This
works fine of SQL Server but not under Access 2003. Is there something wrong
with the statement, or Access can't handle this kind of statement? What's
the alternative?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top