Query translate from MySQL to Access

[Lirsy]

Hi!!

I am becoming crazy trying to pass a sentence from MySQL to Access.

The issue is as follows:

I have one table (BoQ) with codes (Cod) and quantities (Q) and another table (BD) with codes (Cod) and invoices (Sub).

I want a query that gives me one line for each code, containing the code, the sum of all quantities for the code and the sum of all invoices for the code.

I have written the following MySQL sentence that looks like working:

select
B.Cod, sum(B.q) as Quan, if(isnull(J.Cost),0,J.Cost) as Rate,
if(isnull(sum(B.q) * j.Cost),0,sum(B.q) * j.Cost) As Cost
from BoQ AS B
left join (
SELECT Cod, sum(Sub) Cost
FROM BD GROUP BY BD.Cod) AS J
on b.Cod = j.Cod GROUP BY B.Cod;

The query looks like working .... but it absolutelly looks like I am unable to translate it to access.

May anyone help me with that??

Thank you so much in advance!!!

[jforbes]

When using the GROUP BY keyword, any field in the SELECT portion for the statement needs to be accounted for either in the GROUP BY or with an Aggregate Function. Neither Rate nor Cost are an Aggregate or in the GROUP BY.

You've also got an IF() function, which should probably be IIF() function. or use a NZ() function if you are looking to provide a Zero instead of a Null

From what it looks like your attempting, you will probably need to include another SubQuery or reorder the query to only have one GROUP BY. This might work, I have my doubts, but it should get you closer:

SELECT
  B.Cod
, NZ(J.Quan,0) AS Quan
, NZ(J.Cost,0) AS Rate
, NZ(J.Quan * j.Cost,0) AS Cost
FROM BoQ AS B
LEFT JOIN (
  SELECT 
    Cod
  , SUM(Sub) AS Cost
  , SUM(B.q) AS Quan
  FROM BD 
  GROUP BY BD.Cod) AS J
ON b.Cod = j.Cod  

[Lirsy]

Thank you VERY much!! Let me go for in now!! :-)

BTW, quite better ussing Nz!!