Duplicate that flags Case Change

[susanmgarrett]

I'm using the standard duplicates query on a field called trackTitle:

SELECT TrackTitles.trackTitle, TrackTitles.akTrack
FROM TrackTitles
WHERE (((TrackTitles.trackTitle) In (SELECT [trackTitle] FROM [TrackTitles] As Tmp GROUP BY [trackTitle] HAVING Count(*)>1 )))
ORDER BY TrackTitles.trackTitle;

I need to be flagged if there's a title in a different case - for example, the track "NEXT Year" might have versions appear as "Next Year" or "next year". These versions would need to be flagged and then corrected by hand.

Eyeballing a list of 4,000 new tracks isn't cutting it.

If I could get the duplication to be case sensitive, I could then use a left([trackTitle], 5) to weed down the review list - much easier to pick out the thorns.

Any ideas on how I could make that duplicates query case sensitive?

Thanks.

 

[ajetrumpet]

Susan, if you're looking for any records that are not in proper case format, use this query to retrieve them (it will find records that have complete word strings in lower or UPPER case...

SELECT TrackTitles.trackTitle, TrackTitles.akTrack, 

Switch(StrComp(UCASE([trackTitle]),[trackTitle],0) = 0, "Contains UPPER CASE", 
   StrComp(LCASE([trackTitle]),[trackTitle],0) = 0, "Contains lower case")
      AS [Case Check], 

         Switch([Case Check]="Contains lower case", StrConv([trackTitle], 3),
            [Case Check] = "Contains UPPER CASE", StrConv([trackTitle], 3))
               AS [Proper Format]

FROM TrackTitles

WHERE (((TrackTitles.trackTitle) In (SELECT [trackTitle] FROM [TrackTitles]
   As Tmp GROUP BY [trackTitle] HAVING Count(*)>1 )))

ORDER BY TrackTitles.trackTitle;

...then use an update query to change them...

UPDATE [Case Change Query] SET 

   [Case Change Query].[trackTitle] = 
      IIf([Proper Format] Is Not Null, [Proper Format], [trackTitle]);

The Microsoft article is here.