?
Solved

Help with query

Posted on 2013-05-31
9
Medium Priority
?
227 Views
Last Modified: 2013-07-06
I have a query
SELECT [1 DairyCrest Master Data V2].[Oracle No], [1 DairyCrest Master Data V2].Group, [1 DairyCrest Master Data V2].[New Description], [1 DairyCrest Master Data V2].[New Long Description], [1 DairyCrest Master Data V2].Manufacturer, [1 DairyCrest Master Data V2].[Manufacturers Item No], [1 DairyCrest Master Data V2].Noun, [1 DairyCrest Master Data V2].Attribute1, [1 DairyCrest Master Data V2].Attribute2, [1 DairyCrest Master Data V2].Attribute3, [1 DairyCrest Master Data V2].Attribute4, [1 DairyCrest Master Data V2].Attribute5, [1 DairyCrest Master Data V2].Attribute6, [1 DairyCrest Master Data V2].Attribute7, [1 DairyCrest Master Data V2].Attribute8, [1 DairyCrest Master Data V2].Attribute9, [1 DairyCrest Master Data V2].[Old Description]
FROM [1 DairyCrest Master Data V2]
WHERE ((([1 DairyCrest Master Data V2].[Oracle No]) In (SELECT [Oracle No] FROM [1 DairyCrest Master Data V2] As Tmp GROUP BY [Oracle No] HAVING Count(*)>1 )))
ORDER BY [1 DairyCrest Master Data V2].[Oracle No];

I would like to see only the records where the New Decription field is not the same

What criteria do I need to add to the New Decription column

Gordon
0
Comment
Question by:GiaHughes
[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
  • 5
  • 3
9 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 39211027
How about :

Select  [1 DairyCrest Master Data V2].[New Description], Count ( [1 DairyCrest Master Data V2].[New Description]) FROM [1 DairyCrest Master Data V2] group by [1 DairyCrest Master Data V2].[New Description]

that will show you ALL the New Desctiption values and how many of each there are

AW
0
 

Author Comment

by:GiaHughes
ID: 39211890
Hi

I am looking to see only the records where the description is different so that I can amend the description to be the same for each unique oracle number

Gordon
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 39211999
What do you mean by "different" - that could mean comparing the first record with the last record, for example.

Can you show an example of what you want to accomplish?

AW
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:GiaHughes
ID: 39212070
Hi AW

Ok
Oracle no 1234567 may appear 2 or more times in the db
If the descriptions are all the same I do not want to see these records

But if it has different new descriptions for example
Test
Test2
Test3

I want to see these records so that I could change them all to read Test as an example

The current query only looks at where the oracle number is duplicated and I have to look at 3000 records manually to see if the descriptions are different

Hope this explanation is ok

Gordon
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 39212122
but you would seem to allow the possibility that there could be 4 records, but two different values for New Description:

Test
Test1
Test
Test1

what would you do then?

AW
0
 

Author Comment

by:GiaHughes
ID: 39212525
Hi AW

I would want to see this
Only want to not see where all the new descriptions are the same
This is so that I can modify the descriptions to be the same for an oracle number

Gordon
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39218065
Can you try this?
SELECT [1 DairyCrest Master Data V2].[Oracle No], 
       [1 DairyCrest Master Data V2].Group, 
       [1 DairyCrest Master Data V2].[New Description], 
       [1 DairyCrest Master Data V2].[New Long Description], 
       [1 DairyCrest Master Data V2].Manufacturer, 
       [1 DairyCrest Master Data V2].[Manufacturers Item No], 
       [1 DairyCrest Master Data V2].Noun, 
       [1 DairyCrest Master Data V2].Attribute1, 
       [1 DairyCrest Master Data V2].Attribute2, 
       [1 DairyCrest Master Data V2].Attribute3, 
       [1 DairyCrest Master Data V2].Attribute4, 
       [1 DairyCrest Master Data V2].Attribute5, 
       [1 DairyCrest Master Data V2].Attribute6, 
       [1 DairyCrest Master Data V2].Attribute7, 
       [1 DairyCrest Master Data V2].Attribute8, 
       [1 DairyCrest Master Data V2].Attribute9, 
       [1 DairyCrest Master Data V2].[Old Description] 
  FROM [1 DairyCrest Master Data V2] 
 WHERE [1 DairyCrest Master Data V2].[Oracle No] IN (SELECT [Oracle No] 
                                                       FROM (SELECT DISTINCT [Oracle No], 
                                                                             [New Description]
                                                               FROM [1 DairyCrest Master Data V2]) AS T1
                                                      GROUP BY [Oracle No], 
                                                               [New Description] 
                                                     HAVING COUNT(*) > 1) 
 ORDER BY [1 DairyCrest Master Data V2].[Oracle No]; 

Open in new window

0
 

Accepted Solution

by:
GiaHughes earned 0 total points
ID: 39290316
Cannot get any of these solutions to work
0
 

Author Closing Comment

by:GiaHughes
ID: 39303684
Doing it manually
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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 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…
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…
Suggested Courses

762 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