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.