SQL Query help for ASP code

In this query, I am trying to get the top 5 shops in the table based on the products liked to them.
It works well until I try to query the Products table to see if the product is active.

          strsqlPC = "SELECT top 5 shopid,Count(*) as numCount FROM Shop2Products WHERE productid=(SELECT productID from Products WHERE prodActive='Active') GROUP BY shopid ORDER BY Count(*) DESC"


What I get is:
Microsoft JET Database Engine error '80004005'
At most one record can be returned by this subquery.

I am running ASP code which is querying an Access Database.
JuniorBeeAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
      strsqlPC = "SELECT top 5 shopid,Count(*) as numCount FROM Shop2Products WHERE productid IN (SELECT productID from Products WHERE prodActive='Active') GROUP BY shopid ORDER BY Count(*) DESC"
0
 
JuniorBeeAuthor Commented:
Brilliant angelIII.  Thanks!!!
=)
0
 
JuniorBeeAuthor Commented:
Learned something new.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.