Solved

How to run the following query in MS Access?

Posted on 2011-02-24
9
338 Views
Last Modified: 2012-05-11
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
Comment
Question by:checkmofoshoduno
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Select V_code, count(P_Code) as PCount from
(Select distinct V_code, P_code from Product) as q1
Group By V_code
0
 

Author Comment

by:checkmofoshoduno
Comment Utility
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
 

Author Comment

by:checkmofoshoduno
Comment Utility
Attached Access DB
Ch07-E4.mdb
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:checkmofoshoduno
Comment Utility
Kudos
0
 

Author Closing Comment

by:checkmofoshoduno
Comment Utility
thank you.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
too slow :)
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now