Duplicate fields in Access Database

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.
 
V

vanderghast

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
 
K

Krzysztof Naworyta

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 :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top