[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Easy bit of SQL

Posted on 2011-02-25
8
Medium Priority
?
273 Views
Last Modified: 2012-05-11
Take a table with 2 fields :
ProductCode
UniqPrice

select all records with UniqPrice=Y where the ProductCode occurs more than once.

(This is an error checking mechanism.  I want to ensure each ProductCode occurs only once with the UniqPrice flag =Y)
0
Comment
Question by:Patrick O'Dea
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1200 total points
ID: 34984339
Like so:

SELECT Table1.ProductCode
FROM Table1
WHERE (((Table1.UniqPrice)="Y"))
GROUP BY Table1.ProductCode
HAVING (((Count(Table1.ProductCode))>1));


mx
0
 
LVL 9

Assisted Solution

by:rawinnlnx9
rawinnlnx9 earned 400 total points
ID: 34984341
Select * From Table1 WHERE UniqPrice='Y' AND ProductCode HAVING COUNT(*) > 1
0
 
LVL 33

Assisted Solution

by:jppinto
jppinto earned 400 total points
ID: 34984353
SELECT Count(YourTableName.ProductCode) AS CountOfProductCode, YourTableName.ProductCode
FROM [YourTableName]
WHERE (((YourTableName.UniqPrice)=Y))
GROUP BY YourTableName.ProductCode
HAVING (((Count(YourTableName.ProductCode))>1));
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 9

Expert Comment

by:rawinnlnx9
ID: 34984383
Shoot, blew my query. The right answer is first.
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34984399
"the right answer is first" ....and last! :)
0
 
LVL 75
ID: 34984468
".and last! :)"
2 minutes later :-)

mx
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 34984493
Thanks
0
 
LVL 75
ID: 34984536
21Dewsbury: ... with all due respect, there was no need for a Split here
And the Assist by rawinnlnx9 does not even work.
And jppinto posted the same thing I did

mx
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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