HasS HasS - 5 months ago 15
SQL Question

MySQL Query to MS Access Query

Hello I have been working with a project to MySQL database and I am converting it to MS ACCESS database.

Can someone help me for the equivalent of this query from MySQL to MS ACCESs SQL?
Becouse my ms access query does not work.

The MySQL Query :


Select count(u.phone) as ordernr, u.firstname , u.lastname ,
u.address from user u join orders o on u.phone = o.phone group by
firstname ;


MS ACCESS Query (is not working) which i tryed so far:


Select count(u.phone) as ordernr, u.firstname as firstname,
u.lastname as lastname , u.address as add from user u inner join orders
o on u.phone = o.phone group by firstname ;


is giving me this error message : your query does not include the specified expression 'lastname' as part of an aggregate function.

Looking forward for your help and/or suggestions! Thank you

Answer

The correct MS Access version is:

Select count(u.phone) as ordernr, u.firstname, u.lastname, u.address
from [user] as u inner join
     orders as o
     on u.phone = o.phone
group by u.firstname, u.lastname, u.address;

Note:

  • user is a reserved word in MS Access.
  • join operation needs to include the inner.
  • as is needed for table aliases.
  • The group by needs to include all columns (that is a SQL-thing true of all databases except MySQL).
Comments