By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,588 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Query translate from MySQL to Access

P: 6
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!!!
Feb 27 '17 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   B.Cod
  3. , NZ(J.Quan,0) AS Quan
  4. , NZ(J.Cost,0) AS Rate
  5. , NZ(J.Quan * j.Cost,0) AS Cost
  6. FROM BoQ AS B
  7. LEFT JOIN (
  8.   SELECT 
  9.     Cod
  10.   , SUM(Sub) AS Cost
  11.   , SUM(B.q) AS Quan
  12.   FROM BD 
  13.   GROUP BY BD.Cod) AS J
  14. ON b.Cod = j.Cod  

Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.   B.Cod
  3. , NZ(J.Quan,0) AS Quan
  4. , NZ(J.Cost,0) AS Rate
  5. , NZ(J.Quan * j.Cost,0) AS Cost
  6. FROM BoQ AS B
  7. LEFT JOIN (
  8.   SELECT 
  9.     Cod
  10.   , SUM(Sub) AS Cost
  11.   , SUM(B.q) AS Quan
  12.   FROM BD 
  13.   GROUP BY BD.Cod) AS J
  14. ON b.Cod = j.Cod  
Feb 27 '17 #2

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

BTW, quite better ussing Nz!!
Feb 28 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.