In access I do not want to duplicate items on a report.

[Tim A]

I am a karaoke DJ. I have over 50,000 songs in my database. I want to create a report to make a book listing all my songs for people to select a song to sing. Many of the songs are duplicated several times on my database. How do I make a report showing the Song Title only once and not all the duplicates?

[jimatqsi]

It would be better if we could see the table or query that your report is based on. But in general, in the report's query, group results by Song title. That will produce only one entry in the data set for each song title.

If your data set includes some other value, like "album name", then you could get multiple listings if the song appears on multiple albums. The solution then could be to not include album name in your result set.

However, if the song is by more than one group, maybe you want that song appearing more than once, but not more than once for each song/artist combination.

It's difficult to give any definitive help without knowing more about the underlying tables/queries and your knowledge level.

Jim

[Tim A]

It would be better if we could see the table or query that your report is based on. But in general, in the report's query, group results by Song title. That will produce only one entry in the data set for each song title.

If your data set includes some other value, like "album name", then you could get multiple listings if the song appears on multiple albums. The solution then could be to not include album name in your result set.

However, if the song is by more than one group, maybe you want that song appearing more than once, but not more than once for each song/artist combination.

It's difficult to give any definitive help without knowing more about the underlying tables/queries and your knowledge level.

Jim

This is a sample of my table.
As you can see there are multiple songs on different discs with same and different artists.
Thanks Jim,
Rick

ID ARTIST TRACK DISC DISC#
57 10 Years Beautiful CB5123 01
58 10 Years Beautiful Cb30078 07
59 10 Years Wasteland SC8966 13
61 10,000 Maniacs More Than This Sc8390 06
63 10,000 Maniacs Trouble Me Sc8439 03
66 10Cc I'm Not In Love Sc8417 13
67 10Cc We Do For Love Sc8456 15
68 112 Come See Me Sc8357 10
69 112 Dance With Me Sc8726 09
70 112 Dance With Me CBEP473 3
71 112 It's Over Now Sc8672 15
72 112 Only You Sc8295 04
74 112 Peaches & Cream SGB0065 18
73 112 Peaches & Cream SGB65 18
75 112 Peaches And Cream Sc8702 02

[jimatqsi]

select Track from sometablename GROUP BY Track

will give you a list of all your track names once each

select Artist, Track from sometablename GROUP BY Artist, Track

will give you a list of all your track names, once for each artist performing the track

Use one of these for your query underlying the Report.

Jim

[NeoPa]

Using GROUP BY would certainly work, but I would suggest using the DISTINCT predicate of the SELECT clause.

SELECT DISTINCT Artist
               ,Track
FROM            YourTable

Another way to effect this is to enable Unique Values in the QueryDef's properties.

[Tim A]

select Track from sometablename GROUP BY Track

will give you a list of all your track names once each

select Artist, Track from sometablename GROUP BY Artist, Track

will give you a list of all your track names, once for each artist performing the track

Use one of these for your query underlying the Report.

Jim

THANKS,

WORKED GREAT!
Tim

[jimatqsi]

I'll have to try that Distinct Values thing. I'd like to compare performance results to what I have been doing.

Thanks for the tip,
Jim