Solved Sorting with DISTINCT

[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?

 

[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.

 

[lodmark]

Cascading combo boxes would solve my problem.
But as it works today when I only select the artists from the artist table and with a connection to record_ID sorted by release year, I get all the records that that artist has released and that I have in my collection one after the other when I scroll with the form's arrows.
I think it's pretty neat.
@Pat Hartman
I think my tables work and that with enforced RI. That is, the most important tables such as artist, record (album) and music (Song).
I know that I don't have a good grasp of genre or media, it doesn't seem to be included in all forms or is different as you point out.
This needs to be solved.
But next week I'll look at cascading combo boxes, I think.
Have a nice weekend everyone.

 

[Ken Sheridan]

But next week I'll look at cascading combo boxes, I think.

You might find the attached little demo file useful. Note that where data is in a tree structured hierarchy, only the lowest level in the hierarchy should be stored in a referencing table, city in the demo. Otherwise redundancy is introduced and the table is not normalized to Third Normal Form (3NF), leaving the table open to update anomalies. The values in the upper levels should be returned in unbound controls, as illustrated in the demo.

 

[lodmark]

Hi
Now I have solved the problem with searching for my records based on the artist and managed to sort them correctly. Almost in any case.
Of course I used cascading combo boxes as you suggested.

I tried an alternative solution first but here I couldn't get the sorting correct.

Thanks everyone for the help and all the happy shouts.
Leif