Solved

How to run the following query in MS Access?

Posted on 2011-02-24
9
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 34971937
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
ID: 34972003
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
ID: 34972026
Attached Access DB
Ch07-E4.mdb
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 34972052
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
 

Author Comment

by:checkmofoshoduno
ID: 34972060
Kudos
0
 

Author Closing Comment

by:checkmofoshoduno
ID: 34972070
thank you.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34972096
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
ID: 34972103
too slow :)
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34972127
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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