By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,287 Members | 1,582 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,287 IT Pros & Developers. It's quick & easy.

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

P: 3
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?
Jul 26 '10 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 1,240
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
Jul 26 '10 #2

P: 3
@jimatqsi
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
Jul 26 '10 #3

Expert 100+
P: 1,240
Expand|Select|Wrap|Line Numbers
  1. select Track from sometablename GROUP BY Track
will give you a list of all your track names once each

Expand|Select|Wrap|Line Numbers
  1. select Artist, Track from sometablename GROUP BY Artist, Track
  2.  
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
Jul 26 '10 #4

NeoPa
Expert Mod 15k+
P: 31,768
Using GROUP BY would certainly work, but I would suggest using the DISTINCT predicate of the SELECT clause.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Artist
  2.                ,Track
  3. FROM            YourTable
Another way to effect this is to enable Unique Values in the QueryDef's properties.
Jul 27 '10 #5

P: 3
@jimatqsi
THANKS,

WORKED GREAT!
Tim
Jul 27 '10 #6

Expert 100+
P: 1,240
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
Jul 27 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.