Duplicate fields in Access Database

  • Thread starter Thread starter Eagle44golf
  • Start date Start date
E

Eagle44golf

I have an Access database of all of my CD's that has all of the tracks. I
want to have a query that will show which songs I have that are done by
multiple artists. I can't use "Find duplicates" query since I might have the
same song by the same artist on multiple CD's. What I want is to be able to
have a query that gives me duplicate songs with different artists only.
 
Make a total query like:

SELECT songTitle,
songArtistID,
LAST(songCDTitle),
LAST(songCDTrackNumber),
1< COUNT(*) AS isOnAnotherCDtooSameSongSameArtist
FROM songs
GROUP BY songTitle, songArtistID


Vanderghast, Access MVP
 
Juzer Eagle44golf <[email protected]> napisa³
| I have an Access database of all of my CD's that has all of the tracks.
| I want to have a query that will show which songs I have that are done
| by multiple artists. I can't use "Find duplicates" query since I might
| have the same song by the same artist on multiple CD's. What I want is
| to be able to have a query that gives me duplicate songs with different
| artists only.


Select * from Songs s
where exists
(
select song
from
(select song, artist from Songs group by song, artist) x
group by song
having
count(artist)>1
and
s.song = x.song
)

Hope you have fields [song] and [artist] indexed :)
 
Back
Top