MySQL Query to MS Access Query

Question

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

Gordon Linoff · Accepted Answer · 2016-06-12 14:50:11Z

2

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).

edited Jun 12, 2016 at 14:50

answered Jun 12, 2016 at 14:47

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Gordon Linoff Over a year ago

@HasS . . . I just updated the answer. I think I have all the access'isms accounted for.

HasS Over a year ago

Hmm it worked , thanks a lot for your help, i have many other queries that i want to convert and this helped a lot :) @Gordon Linoff

HasS Over a year ago

if you can help me on this one too , im getting a strange behaviour , and I think there is nothing wrong with this query :

Select i.itemname, i.id, i.price as pricexitem,oil.quantity , (oil.quantity * i.price) as price from orders as o inner join orderitemlist as oil on oil.orderid = o.id inner join item as i on i.id = oil.itemid where o.id= 2 ;

Gord Thompson Over a year ago

Minor correction: Access SQL does not require the AS keyword for table aliases.

Gord Thompson Over a year ago

@HasS - That is a different issue. Please ask a new question.

|

Giorgos Betsos · Accepted Answer · 2016-06-12 14:47:08Z

2

You have to include in the GROUP BY clause every non-aggregated field present in the SELECT clause:

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

answered Jun 12, 2016 at 14:47

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

1 Comment

HasS Over a year ago

Hello giorgos , thank you for your answer, the query you wrote is not working maybe becose 'user' is reserved from the ms access and it needs to be [user] but all the other parts of query is totally ok . Best regards

Collectives™ on Stack Overflow

MySQL Query to MS Access Query

2 Answers 2

7 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related