Solved

How to run the following query in MS Access?

Posted on 2011-02-24
9
385 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 93

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 93

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

626 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