1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: MS Access SQL systax error

Discussion in 'Business Applications' started by billgates2000, Jan 3, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. billgates2000

    billgates2000 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    15
    Hi guys,

    I'm moving my database from a mysql server to a local access database. I have trouble with one of my queries, which I didn't manage to make it work in Access. The original mysql query is as follows:

    SELECT id, posted_for, COUNT(*) cnt FROM comments GROUP BY posted_for ORDER BY cnt DESC LIMIT 20;

    In Access, I syntaxed the above like this, but it returns no results:

    SELECT TOP 20 id, posted_for, COUNT(*) cnt FROM comments GROUP BY posted_for ORDER BY cnt DESC;

    Any ideas on how I can resolve this issue?
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Hey BG2000, I got this syntax to work in access. Maybe that will help.

    SELECT TOP 3 Count(EMail03.ID) AS cnt, EMail03.Department
    FROM EMail03
    GROUP BY EMail03.Department
    ORDER BY Count(EMail03.ID) DESC;
     
  3. billgates2000

    billgates2000 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    15
    Looks like I'm still having problems with my case... Even the following simplified query gives me an error:

    SELECT TOP 20 id, posted_for, COUNT(id) AS cnt FROM comments;

    Gives: Microsoft JET Database Engine- Error '80040e21' - You tried to execute a query that does not include the specified expression 'id' as part of an aggregate function.

    Thanks.
     
  4. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Need a "group by" (aggregate function) to count.

    You will need an "Order by" if you want the largest numbers to be part of the "Top 20'.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You may find this easier to do with the Query in Design View and Select Totals. Then use the Top Value Property to set the required number of records.
     
  6. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Thanks OBP, where is this 'Top Value Property' you speak of?

    Design view is easiest for me to start with. Had to go to SQL view to add the "TOP 3" parameter.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You have to click on the Query below the Criteria Rows in design view to get the Query Properties to come up. Top Values is the 3rd down from the top.
     
  8. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I see maybe, in 2010 its Max records? think I like sql coding it better but I learned something!
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Actually Max is a different function, it only finds the max value of a Group, whereas Top Value shows the top "N" records of any recordset.
     
  10. billgates2000

    billgates2000 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    15
    Ok thanks guys, this now works and it gives correct results:

    SELECT TOP 20 posted_for, COUNT(id) AS cnt FROM comments GROUP BY posted_for ORDER BY COUNT(id) DESC;

    The problem is that I need to have the 'id' field selected as well, and the following attempt doesn't work:

    SELECT TOP 20 id, posted_for, COUNT(id) AS cnt FROM comments GROUP BY posted_for ORDER BY COUNT(id) DESC;

    (it gives me the error message "You tried to execute a query that does not include the specified expression 'id' as part of an aggregate function")

    Thanks.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What happens if you add the ID as a "Group By"?
     
  12. billgates2000

    billgates2000 Thread Starter

    Joined:
    Nov 11, 2010
    Messages:
    15
    it seems that it returns all records... it doen't group them properly... that's the SQL I used:

    SELECT TOP 20 id, posted_for, COUNT(id) AS cnt FROM comments GROUP BY posted_for, id ORDER BY COUNT(id) DESC;
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What happens if you put the id before the Posted_for?
    Another possibility is to have a second querybased on the first query that adds the ID back in to the Query.
     
  14. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Wen you 'group by' ID it will group on ID as well as comments. Keep in mind that if ID is a unique record identifier that group will change, sometimes dramatically to one record per ID. that seems like where you are at here. Pick something other than ID to group by.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    He needs ID field in the Query recordset, the only way to have it is Group By, unless he adds it later.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083506

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice