Error in expression ''

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
 
G

Gary Walter

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

Ivan Debono

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?
Click to expand...
Click to expand...
 
Top