Easy bit of SQL

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)
Patrick O'DeaAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Like so:

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


mx
0
 
rawinnlnx9Connect With a Mentor Commented:
Select * From Table1 WHERE UniqPrice='Y' AND ProductCode HAVING COUNT(*) > 1
0
 
jppintoConnect With a Mentor Commented:
SELECT Count(YourTableName.ProductCode) AS CountOfProductCode, YourTableName.ProductCode
FROM [YourTableName]
WHERE (((YourTableName.UniqPrice)=Y))
GROUP BY YourTableName.ProductCode
HAVING (((Count(YourTableName.ProductCode))>1));
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
rawinnlnx9Commented:
Shoot, blew my query. The right answer is first.
0
 
jppintoCommented:
"the right answer is first" ....and last! :)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
".and last! :)"
2 minutes later :-)

mx
0
 
Patrick O'DeaAuthor Commented:
Thanks
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.