• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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;
0
checkmofoshoduno
Asked:
checkmofoshoduno
  • 4
  • 2
  • 2
  • +1
1 Solution
 
peter57rCommented:
Select V_code, count(P_Code) as PCount from
(Select distinct V_code, P_code from Product) as q1
Group By V_code
0
 
checkmofoshodunoAuthor Commented:
That returns:

(whats attached in the image)




mySQL returned:

+
| V_CODE | COUNT(DISTINCT (P_CODE)) |
+- - - - - - - - - - - - - - - - - - - - - - - - - - +- - - - - - - - +
| 21344  |                        3 |
| 24288  |                        3 |
| 25595  |                        3 |
+ results of access
0
 
checkmofoshodunoAuthor Commented:
Attached Access DB
Ch07-E4.mdb
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
SharathData EngineerCommented:
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
0
 
checkmofoshodunoAuthor Commented:
Kudos
0
 
checkmofoshodunoAuthor Commented:
thank you.
0
 
Patrick MatthewsCommented:
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2417-Calculating-Distinct-Counts-in-Access.html
0
 
Patrick MatthewsCommented:
too slow :)
0
 
peter57rCommented:
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
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now