Error in expression ''

[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

 

[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

 

[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

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?