Is there any way I can show that a record has a twin elsewhere?

[cherold]

I have a list of songs on albums. In some cases the same song is on more than one album, and I'm wondering if there's a simple way to indicate that in a query? For example, by adding an asterisk.

So, I have two tables:

Songs {
songID (autonumber)
title (text)
}
lp_contents {
SongKeyID (autonumber), 
song_id (number)
LP_id (number)
}

To find all the songs on album #1076 I can query:

SELECT Songs.Title, LPContents.LP_id, LPContents.song_id
FROM Songs INNER JOIN LPContents ON Songs.SongID = LPContents.song_id
WHERE LPContents.LP_id=1076;

LPContents can have multiple references to the same song, so in this list, songs 6 & 10 show up twice.

SongKeyID      song_id     LP_id
1                    6              1076
2                    6               20
3                    7               1076
4                    8               1076
5                    9               20
6                   10               1076
7                   10               20

Is there a way to put an asterisk next to any song_id that exists more than once in LPContents? So that a list of songs from album 1076 could display as:

Songs.Title    LPContents.song_id
(*)song 1        6      
song 2           7
song 3           8
(*)song 5        10

Is such a thing possible within SQL? Or will I have to create a function so I can do something like:

SELECT IsDoubled(LPs.song_id), Songs.Title, LPContents.LP_id, LPContents.song_id
FROM Songs INNER JOIN LPContents ON Songs.SongID = LPContents.song_id
WHERE LPContents.LP_id=1076;

[isladogs]

I suggest you add a Boolean (yes/no) field to the LPContents table and call it Dupe or similar.
Then create a duplicates query and set the Dupe field =true for all records identified using an update qery

[orange]

An sql approach

SELECT songkeyid
	,lp_id
	,song_id
FROM lpContent
WHERE song_id IN (
		SELECT song_id
		FROM (
			SELECT Count(LpContent.Song_ID) AS CountOfSongID
				,LpContent.song_id
			FROM LpContent
			GROUP BY LpContent.song_id
			HAVING (((Count(LpContent.Song_ID)) > 1))
			)
		);

songkeyid	lp_id	song_id
1	1076	6
2	20	6
6	1076	10
7	20	10

[Beetle]

To expand a bit on what was already posted, in order to get the preceding characters you want for those songs, you're going to need to add an IIf statement somewhere in the mix.

If using Colin's approach with the Boolean field, then it might look like;

SongTitle: IIf([Dupe]=True, "(*)" & [Title], [Title])

If using the SQL approach like Orange suggested;

SongTitle: IIf([CountOfSongID] > 1, "(*)" & [Title], [Title])

[orange]

I was only focusing on lpcontents.

Here's modified sql to put the (*) beside the song_id

SELECT songkeyid, lp_id, "(*)" & song_id as DupSongIndicator
FROM lpContent
WHERE song_id IN (
        SELECT song_id
        FROM (
            SELECT Count(LpContent.Song_ID) AS CountOfSongID
                ,LpContent.song_id
            FROM LpContent
            GROUP BY LpContent.song_id
            HAVING (((Count(LpContent.Song_ID)) > 1))
            )
        );

songkeyid	lp_id	DupSongIndicator
1	1076	(*)6
2	20	(*)6
6	1076	(*)10
7	20	(*)10

[cherold]

Unfortunately my SQL is not good enough for me to figure out how to join the above query of LPContents with my own query of Songs + LPContents. How do I combine my own original query of

SELECT Songs.Title, LPContents.LP_id, LPContents.song_id
FROM Songs INNER JOIN LPContents ON Songs.SongID = LPContents.song_id
WHERE LPContents.LP_id=1076;

with this one?

SELECT songkeyid, lp_id,song_id
FROM lpContent
WHERE song_id IN (
SELECT song_id
FROM (
SELECT Count(LpContent.Song_ID) AS CountOfSongID, LpContent.song_id
FROM LpContent
GROUP BY LpContent.song_id
HAVING (((Count(LpContent.Song_ID)) > 1))));

[Beetle]

Try this;

SELECT IIf([DupeTitle]>1,"(*)" & [Title],[Title]) AS SongTitle, (Select Count(SongID) From LPContents Where Songs.SongID = LPContents.SongID) AS DupeTitle
FROM Songs;