Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Easy bit of SQL

Posted on 2011-02-25
8
Medium Priority
?
280 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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

581 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