Sorting with DISTINCT (1 Viewer)

[lodmark]

Hi!
I have a question regarding the appearance of the dropdown list for a search function.
I have collected the fields I need for the search in a question, see image.

Now I just need to make the list distinct so that the appearance in the dropdown list does not contain more than one example of the artist.

I have tested with different SQL statements where I have used DISTINCT and GROUP BY but have not found the right combination.

The selection that gives the list above looks like this:

SELECT DISTINCT
tbl_record.record_ID,
tbl_artist_1.artist_name,
tbl_record.record_release_date
FROM
(
tbl_artist AS tbl_artist_1
INNER JOIN tbl_music ON tbl_artist_1.artist_ID = tbl_music.music_artist_ID
)
INNER JOIN (
tbl_record
INNER JOIN tbl_music_record ON tbl_record.record_ID = tbl_music_record.music_record_record
) ON tbl_music.music_ID = tbl_music_record.music_record_music
ORDER BY
tbl_artist_1.artist_name,
tbl_record.record_release_date;

Anyone think they can find a SQL statement that will give me a list of just one of each artist?

Leif

 

[arnelgp]

record_id is autonumber and is unique, so you will end up with All the records returned.
use Total Query:

Select Min(record_id) As minRec, artist_name, Min(record_release) from yourTable Group by artist_name;

 

[isladogs]

Or just use SELECT DISTINCT omitting the Record_ID field

 

[Gasman]

Or just use SELECT DISTINCT omitting the Record_ID field

And the ReleaseDate surely?

DISTINCT means for the record?

 

[isladogs]

And the ReleaseDate surely?

Yes indeed! That gives one of each artist!

 

[lodmark]

Hey!

That was almost embarrassingly easy. Thanks.

But when I try your SQL statement as a row source in my combo box, I get the following error message:

The SQL statement I entered looks like below and it works as long as I'm in design mode.

SELECT
Min(qryRecord_Search_Form_record2.record_id) AS minRec,
qryRecord_Search_Form_record2.artist_name,
Min(qryRecord_Search_Form_record2.record_release_date) AS minRel
FROM
qryRecord_Search_Form_record2
GROUP BY
qryRecord_Search_Form_record2.artist_name;

 

[plog]

Is 'artist_name' a field in qryRecord_Search_Form_record2? My guess is no, it's that horrible one with a slash and space in it.

 

[lodmark]

Yes indeed! That gives one of each artist!

Yes that would do it.
But I need the Record_ID as a result in the combo box as it's the record of the artist that i'm looking for.
And the release_date for the sorting of the records of that artist.

But it could probably be done in some other way.

 

[plog]

You can't have it both ways.

Either you get a list of unique artists because the artist names are distinct. Or you get duplicate artist names because record_id/artist name is not distinct to artist.

 

[lodmark]

Is 'artist_name' a field in qryRecord_Search_Form_record2? My guess is no, it's that horrible one with a slash and space in it.

YES!

 

[Gasman]

I would have thought, you selected an artist, in perhaps a combo, then a subform or another combo/listbox shows all the records for that artist?

 

[lodmark]

I would have thought, you selected an artist, in perhaps a combo, then a subform or another combo/listbox shows all the records for that artist?

I have a drop down combo at the top of my form for my vinyl records where I can list the artists.
This has worked for a while, it was you people that helped med get it right.
But I missed an artist that only apeared in a record with various artists.
So i had to fix so that even those artist got in the list.

Thats where I am now.

 

[Gasman]

So I would expect
tblArtist
tblAlbum
tblArtistAlbum

The last table would link multiple artists to one album, otherwise the ArtistID would be in tblAlbum?

 

[lodmark]

So I would expect
tblArtist
tblAlbum
tblArtistAlbum

The last table would link multiple artists to one album, otherwise the ArtistID would be in tblAlbum?

 

[Pat Hartman]

But I need the Record_ID as a result in the combo box as it's the record of the artist that i'm looking for.

Since an artist can be associated with multiple records - WHICH RECORD - do you think you are selecting?

For starters, you seem to not have a properly normalized schema. There should be a separate table to hold the names and details for all artists. Then this is the case where you would use cascading combos. Combo1 is from the Artist table where there should be only a single row for each artist. Combo2 is controlled by the selection from Combo1 This is the list from the records table. The RowSource query for Combo2 refers to the selection in Combo1.

Select ... From ... Where ArtistID = Forms!myform!ArtistID
Order By ...

 

[Pat Hartman]

PS. I see you do have an artists table. So, you have simply chosen to use the wrong table for your RowSource.

Try the cascading combos. That should solve your selection problem.

Also, fix your schema to always enforce RI. If you get an error message, then the many side table contains entries that do not have a valid one-side match. You MUST fix the data so you can enforce RI. Either add the missing 1-side record or delete the orphaned many-side record.

And finally, the relationship between genre and music is suspect and should be removed. Logically it allows a conflict since it allows record to have one genre but music to have a different genre.

 

[lodmark]

t's PS. I see you do have an artists table. So, you have simply chosen to use the wrong table for your RowSource.

Try the cascading combos. That should solve your selection problem.

Also, fix your schema to always enforce RI. If you get an error message, then the many side table contains entries that do not have a valid one-side match. You MUST fix the data so you can enforce RI. Either add the missing 1-side record or delete the orphaned many-side record.

Thanks @Pat Hartman I will look into that. But it's a bit to change then...hm.

 

[lodmark]

Hey!

That was almost embarrassingly easy. Thanks.

But when I try your SQL statement as a row source in my combo box, I get the following error message:

View attachment 121715

The SQL statement I entered looks like below and it works as long as I'm in design mode.

SELECT
Min(qryRecord_Search_Form_record2.record_id) AS minRec,
qryRecord_Search_Form_record2.artist_name,
Min(qryRecord_Search_Form_record2.record_release_date) AS minRel
FROM
qryRecord_Search_Form_record2
GROUP BY
qryRecord_Search_Form_record2.artist_name;

Your code works @arnelgp. I've managed to fix the missing punctuation.
But now I've only have the result from the first field in the combo box, the record_ID.
This is despite the fact that the bound column is number two.
When I change to one or three, it still doesn't change in the combo box, it seems to be locked to two.

 

[lodmark]

Your code works @arnelgp. I've managed to fix the missing punctuation.
But now I've only have the result from the first field in the combo box, the record_ID.
This is despite the fact that the bound column is number two.
When I change to one or three, it still doesn't change in the combo box, it seems to be locked to two.

Fix this by changing the order of the tables in design mode.
But now the artists first record doesn't come first, so there still things to fix.

Thanks for today.

 

[Pat Hartman]

But it's a bit to change then...hm.

It is NEVER too much work to fix potentially bad data.

Your code works @arnelgp.

The code doesn't actually work since it doesn't bring back the specific ID you want. Why not simply use the correct table to select artist and add the second combo for record?