MySql to MS Access query conversion

Question

I have a mysql query, which I want to use in MS Access.

SELECT company_name, agent_id FROM
( SELECT company_name, agent_id, @rn := IF(@prev = agent_id, @rn + 1, 1) 
  AS rn, @prev := agent_id FROM users 
  JOIN (SELECT @prev := NULL, @rn := 0) AS vars 
  ORDER BY agent_id DESC, company_name)
 AS T1 
 WHERE T1.agent_id is not null and  rn <= 3;

I have been trying to run it in Access but its throwing the error: "Syntax error in from clause".

That is because these SQL engines have totally different syntaxes. — juergen d, Jun 03 '18 at 20:05
Yes that why I wanted someone to help me with syntax errors, I am unable to resolve the issue — Anurag Verma, Jun 03 '18 at 20:06
Do you necessarily need to convert? There are ways to use MySQL from MS Access. See [this](https://stackoverflow.com/a/5465711/7644018). — Paul T., Jun 03 '18 at 20:12
What exactly did you try to fix the syntax issues? Why didn't you discribe what the query should do? — Shadow, Jun 03 '18 at 21:01
https://support.microsoft.com/en-us/help/210039/acc2000-how-to-create-a-top-n-values-per-group-query — spencer7593, Jun 03 '18 at 21:10

score 0 · Answer 1 · answered Jun 03 '18 at 21:23

In Microsoft Access, we can try using a "TOP n" correlated subquery in the WHERE clause.

I'd try something like this:

 SELECT t.company_name
      , t.agent_id
   FROM [users] AS [t]
  WHERE t.company_name IN
        ( SELECT TOP 3 u.company_name
            FROM [users] AS [u]
           WHERE u.agent_id = t.agent_id
           ORDER
              BY u.company_name
        )
  ORDER
     BY t.agent_id DESC
      , t.company_name

MySql to MS Access query conversion

1 Answers1