How to get rid of dupliacte values in a Query?

[PaulChilds]

Hi,

I have a database of all the songs on my CDs.

I enter the Song title, first line (if different) writer and performing artist.

I want to do a query which brings up each song once:

for example:

At the minute if I had 'People are Strange' sung by the Doors on one CD and Echo and the Bunnymen on another, it comes up with a record for each.

But, in a form based on the query, I want it to recognise that I have the song 'People are Strange' twice and list which CDs I have it on, rather than bring up the two seperate records.

I know there are many different songs with the same title, so I need to make each song unique by recognising more than one of the fields above. (Title, first line & writer)

Any ideas?

Cheers

PC

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[PaulChilds]

It's OK, I know what I need to do to get this started:

I set the total section of the artists column to Count.

http://www.worship-leader.co.uk

http://www.liverpooldream.co.uk

http://www.ormskirkparish.co.uk

http://www.cadetsltd.co.uk

 

[zerosandones]

Hi Paul and Kirsty,

Before we go any further, I'd like to make sure I understand your requirement.

I do understand that you've got a table structure which allows for duplication of title across multiple first line writers ... no problem ... I also assume that one of the fields in your table is called "CD" which identifies which CD the song is on.

Okay ... first, we need to recognize that, unless you're looking to write a function which gathers and concatenates the multiple CD's which a particular song is on, applying a criteria of "People are Strange" will return two database records (by the Doors and by Echo and the BunnyMen).

As such, the resulting display will be inherently "multi-record" unless you write the concatenating function I described.

So ... I envision your form (which is named "frmSong&quot being based upon a query as follows:

SELECT Writer, Artist, CD
FROM tblSong
WHERE Title = [forms]![frmSong]![cboTitle]
ORDER BY Writer;

In the detail section of your form, you'll add three fields for Writer, Artist and CD all bound to their respective "Control Source". The form will be setup as a "Continuous Form" in the "Default View" property.

Next, add a combo box control into the form header or footer (your preference). Make sure that the combo box is not bound to a control source, but, give the combo box the name "cboTitle" in the field's "Name" property. Finally, add the following SQL to the combo box's "Row Source" property:

SELECT DISTINCT Title
FROM tblSong
ORDER BY Title;

Other properties to check in the combo box:

Column Count: 1
Bound Column: 1
Limit to List: Yes

Finally, in the "AfterUpdate" event for the combo box, add the following code:

Private Sub cboTitle_AfterUpdate()

On Error GoTo Err_cboTitle_AfterUpdate

Me.Requery

Exit_cboTitle_AfterUpdate:
Exit Sub

Err_cboTitle_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboTitle_AfterUpdate

End Sub


With all of this in place, when you run the form, initially you won't see any records displayed. Now, select a title from the combo box and voila ... your multiple CDs (if any) will be displayed along with the writer and artist so that you can make your selection to locate your CD.

Hope this helps.

00001111s