Solved Speed issue with ConcatRelated funktion

[MajP]

@loadmark

The form in thread 29 only looks like it does show all of your records so this may be a good candidate

The image in 29 was cropped so I thought this was a search form, but it is a subform. I thought it was all of your records, but it is only those records for a given album. There are only 24 records and will never be much more since these are tracks of an album. In that case you should be fine to put the concat function as a calculated control without any lag. I am assuming it is slow because you put the concat function in the query forcing it to calculate all records every time you requery. If still slow as a calculated control. There are some tricks to really speed it up. In the normal function you open and close the recordset on each call. In this case that is 24 times in the query it is hundreds. What I would then do is modify the code so that it opens the rs once on form open and then close it on form close. (might have to reopen when you modify the performers on a track) Leaving the RS open can make a huge difference on a big dataset. But I doubt with only 24 records there should be any slow issues if you make it a calculated control.

 

[lodmark]

There are only 24 records and will never be much more since these are tracks of an album.

Hi @MajP
I will soon post the file/files.
But I've tried to put the concate function in the field for artist in the subform (frm_record2_subform).
When I first tried the function the only way for me to get the function to work is when the argument (eg. the ID-fields) are taken from the same table or query.
So i created a query called qry_concatenate and put the query in the query for the subform (Query1).

Of course I can se in the form frm_record2 that the specific vinyl record contains only 24 tracks.
But how do I limit these 24 to put in the function?

I will post my original database that you, and others, helped me with for about a year ago.
Together with the one I'm working on now.

The thing is that my original database works perfectly fine, but without the possibility to add tracks with two or more artists.
When I started to look at this possibility I've manually added data so I could test it.
The structure to add a single track to a record that are performed by several artist is still to be discovered.
Sorry for my bad English, I didn't use Google translation this time.

Leif

 

[MajP]

This is very fast. There is a split second flicker.
1. Get rid of the concat query in the subform recordsource.
2. Make the artist control unbound and use this calculation

=getArtists([music_id])

Then put this function in a module

Public Function GetArtists(music_ID As Variant) As String
  Dim rs As DAO.Recordset
  Dim hasMultiples As Boolean
  Dim Criteria As String
  If IsNull(music_ID) Then Exit Function
  Set rs = CurrentDb.OpenRecordset("Select * from qry_Concatenate where Music_ID = " & music_ID)
  'I sorted by music_ID
  Do While Not rs.EOF
    If GetArtists = "" Then
      GetArtists = rs!artist_name
    Else
      GetArtists = GetArtists & ", " & rs!artist_name
    End If
    rs.MoveNext
  Loop
End Function

 

[lodmark]

This is very fast. There is a split second flicker.
1. Get rid of the concat query in the subform recordsource.
2. Make the artist control unbound and use this calculation

=getArtists([music_id])

Result #Name?
But definitly quicker.

If I try to run your query in the function below it says: Syntax Error
If I run this query: Select * from qry_Concatenate where Music_ID = music_ID;
I get a result, but it still says #Name? in the form.

Then put this function in a module

Public Function GetArtists(music_ID As Variant) As String
  Dim rs As DAO.Recordset
  Dim hasMultiples As Boolean
  Dim Criteria As String
  If IsNull(music_ID) Then Exit Function
  Set rs = CurrentDb.OpenRecordset("Select * from qry_Concatenate where Music_ID = " & music_ID)
  'I sorted by music_ID
  Do While Not rs.EOF
    If GetArtists = "" Then
      GetArtists = rs!artist_name
    Else
      GetArtists = GetArtists & ", " & rs!artist_name
    End If
    rs.MoveNext
  Loop
End Function

I can add the working database later if you want. As I said, in this one I can't add any records.
Lets take one thing at a time.

Leif

 

[MajP]

sorry in the subform query you need to pull in Music_ID from one of the tables. Or you can use the foreign key name which is something like
music_record_music
=getArtists([music_record_music])

 

[MajP]

I added a query to show which songs had more than one artist. There are only two as of now.
qry_MultipleArtistSongs qry_MultipleArtistSongs

artist_ID	music_ID	Artist / Grupp	music_titel
10​	3194​	Bee Gees	Introducing Sgt. Pepper's Lonely Hearts Club Band
1478​	3194​	Paul Nicholas	Introducing Sgt. Pepper's Lonely Hearts Club Band
193​	403​	Barbara Streisand	No Moore Tears Enough Is Enough
196​	403​	Donna Summer	No Moore Tears Enough Is Enough
193​	1895​	Barbara Streisand	No Moore Tears Enough Is Enough (Special Disco Version)
196​	1895​	Donna Summer	No Moore Tears Enough Is Enough (Special Disco Version)

In order to test the concat was working I needed to find these. So I added a new FAYT to search by record, and verified the concat.

Also it needed to be compacted. 12M to 3M

 

[lodmark]

sorry in the subform query you need to pull in Music_ID from one of the tables. Or you can use the foreign key name which is something like
music_record_music
=getArtists([music_record_music])

Perfect!
Some issues is yet to overcome, eg. how I can add tracks to a new record as artist or as various.
But this is one step forward.
Many Thanks!
Leif

 

[lodmark]

I added a query to show which songs had more than one artist. There are only two as of now.
qry_MultipleArtistSongs qry_MultipleArtistSongs

artist_ID	music_ID	Artist / Grupp	music_titel
10​	3194​	Bee Gees	Introducing Sgt. Pepper's Lonely Hearts Club Band
1478​	3194​	Paul Nicholas	Introducing Sgt. Pepper's Lonely Hearts Club Band
193​	403​	Barbara Streisand	No Moore Tears Enough Is Enough
196​	403​	Donna Summer	No Moore Tears Enough Is Enough
193​	1895​	Barbara Streisand	No Moore Tears Enough Is Enough (Special Disco Version)
196​	1895​	Donna Summer	No Moore Tears Enough Is Enough (Special Disco Version)

In order to test the concat was working I needed to find these. So I added a new FAYT to search by record, and verified the concat.

Nice! Thanks, swedish?
Yes, for now there's only these two. I'm hoping to be able to add tracks directly with two or more artists on.

When I search for the song I get the tracks doubled? I downloaded your file. Same result.

Leif

 

[MajP]

Yes your query1 needs to get modified. I have to figure it out. But since it includes artist music in the query you will get dupes. Give me a second. I also know how to fix the allow editions/edits.

 

[MajP]

I modified a few things. In your junction tables I got rid of the composite keys. I renamed query1 to qryRecordsSubform.
In the query both the artist table, and artist_music needed to get removed or they would cause duplicates. That information is pulled in through the calculated control. I removed the DISTINCT records from the query which makes it not updateable.

I can add tracks to a new record as artist or as various.

I post one more version after this with a demo.

 

[MajP]

This demos the add multiple artists to a track. There is a button next to the concat field.
After doing it I decided that is not the smartest way. My popup form would instead have a multiselect listbox and it would be searchable. When you open it all the artist on the track would be selected. So here you can add and remove multiple artists at once. The code idea is basically the same since you have to do an insert query either way. I just remember that I have code to automate that. So if I get time I will demo. The way it is set up now you cannot delete from only add to.

 

[lodmark]

You're so quick. I'm amazed.
Thanks @MajP
Haven't had the time to look in to this yet, the time in Sweden is 7:30 PM and I think it would be the night shift for this. I'm so curious.
This is the file containing my working database. Look at the difference between the relationship schemes, then you probably can see what I've done to the one you've been working with.

I'm getting back too you soon.

Thanks again!

Leif

 

[Isaac]

 

[Pat Hartman]

The songs are duplicated in the subform because there are multiple artists. two artists = 2 rows. Three artists = 3 rows, etc. Add grouping to the query and that will eliminate the duplication. It also makes the subform not updateable so you will need a separate form to do updates or add tracks. Just use the double click event of the song titls to open the single record form and edit there.

 

[MajP]

The songs are duplicated in the subform because there are multiple artists. two artists = 2 rows. Three artists = 3 rows, etc. Add grouping to the query and that will eliminate the duplication. It also makes the subform not updateable so you will need a separate form to do updates or add tracks. Just use the double click event of the song titls to open the single record form and edit there

We are well past that. See threads 46, 50,51,
However, I was going to go with a multiselect listbox for adding the multiple artists, but maybe it would be cleaner to do just and song form with a traditional subform for artists. Reduces a lot of coding.

 

[MajP]

Look at the difference between the relationship schemes, then you probably can see what I've done to the one you've been working with.

Are you sure this is the current version. I think your intent is to be able to assign multiple artists to a track, but in this example the junction table
tbl_Music_Artist is missing. That looks older not newer. I would strongly suggest you always version databases. I do V1, V2...
An artist should be assigned to both the Record and to each track. if you want to be able to handle the compilations. You do not want to do what you did prior and have artist names that are compilations "Peter Framption and BeeGees".
My V4 is based off your file in thread 44. That seems to be correct based on what you are desiring.

 

[lodmark]

Are you sure this is the current version. I think your intent is to be able to assign multiple artists to a track, but in this example the junction table
tbl_Music_Artist is missing. That looks older not newer.

Yes, yes, yes. It's an older version.
This is the version I got help with about a year ago.
I posted it because I wanted you to see what it looked like before I started adding what was suggested in this forum.
My problem in this version was just adding tracks performed by more than one artist.

I agree that a version designation would have been good on my files as well.

I am VERY happy with what you have achieved and look forward to the list box that you will add.

Leif

 

[MajP]

I posted it because I wanted you to see what it looked like before I started adding what was suggested in this forum.
My problem in this version was just adding tracks performed by more than one artist.

No need I have been following the discussion, I know where you are and why. It all makes sense to me. This design makes sense. I will add my demo to V4. The only thing would be if you did modify your naming conventions (especially to keys) seen in thread 11, this would be a lot easier for others to understand. You have some really confusing keys and I think this confused people in your initial posts.
music_record_music
music_record_record
That is about as cryptic as it gets.

If it was 'albums' instead of 'record', 'tracks' instead of 'music' this would make a way more sense. I personally like to put an 'fk' behind my foreign key.
so instead of music_record_music it would be Track_ID_FK ( the PK is Track_ID)
and music_record_record would be Album_ID_FK

To me, no one needs a Rosetta Stone to figure out what the Album ID foreign key is.

look forward to the list box that you will add.

For simiplicity I will demo with just a standard form and subform. This will be more flexible and require less code. You click on that track and get a pop up with the main form being track information and the subform being assigned artist. Then you can Add and delete artists in the subform. No code needed. The subform probably needs FAYT combos so you can pick from the large list of artists.

 

[lodmark]

Looking forward too this @MajP

I know that my naming is a bit confusing for others and don't follow expected rules.
That's my beginners fault and a bit of lack of experince.
I've tried to change them a while ago but then I've ended up with a corupted database so I've skipped it.

Is there a simple way to auto change it?
When I change one fk then the corresponding change as well.
But the there's all the code......

Leif

 

[Pat Hartman]

We are well past that. See threads 46, 50,51,

Was there really a need to be snotty? You never told lodmark what caused the problem.