Prev: showing the last 20 results from a union query
Next: Display records one time only
From: Eagle44golf on 16 Mar 2010 11:15
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.
From: vanderghast on 16 Mar 2010 11:56
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




"Eagle44golf" <Eagle44golf(a)discussions.microsoft.com> wrote in message
news:09C173F1-E204-4C53-B160-7814926123CA(a)microsoft.com...
>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.

From: Krzysztof Naworyta on 16 Mar 2010 12:13
Juzer Eagle44golf <Eagle44golf(a)discussions.microsoft.com> 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 :)

--
KN

 | 
Pages: 1
Prev: showing the last 20 results from a union query
Next: Display records one time only