Link to home
Start Free TrialLog in
Avatar of checkmofoshoduno
checkmofoshoduno

asked on

How to run the following query in MS Access?

How do i run this in How to run the following Query in Microsoft Access??

SELECT        V_CODE,
COUNT(DISTINCT (P_CODE))
FROM PRODUCT
GROUP BY   V_CODE
HAVING         COUNT(DISTINCT (P_CODE)) > 2;
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Select V_code, count(P_Code) as PCount from
(Select distinct V_code, P_code from Product) as q1
Group By V_code
Avatar of checkmofoshoduno
checkmofoshoduno

ASKER

That returns:

(whats attached in the image)




mySQL returned:

+
| V_CODE | COUNT(DISTINCT (P_CODE)) |
+- - - - - - - - - - - - - - - - - - - - - - - - - - +- - - - - - - - +
| 21344  |                        3 |
| 24288  |                        3 |
| 25595  |                        3 |
+ User generated image
Attached Access DB
Ch07-E4.mdb
ASKER CERTIFIED SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Kudos
thank you.
Small tweak to Peter's:


Select q1.V_code, count(q1.P_Code) as PCount from
(Select distinct V_code, P_code from Product) as q1
Group By q1.V_code 
HAVING count(q1.P_Code) > 2

Open in new window



For more about distinct counts in Access:

https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2417-Calculating-Distinct-Counts-in-Access.html
too slow :)
WHere did the last line of your query appear from ?  Did you edit the Q after you posted it?


Select V_code, count(P_Code) as PCount from
(Select distinct V_code, P_code from Product) as q1
Group By V_code
Having count(P_Code) >2