Solved Speed issue with ConcatRelated funktion

[gemma-the-husky]

Of course tracks exist independently of records - compilation albums consist of (almost) nothing but tracks plucked out of their original home. Tracks on albums may be released as singles etc.

If it is important to keep track (sorry) of multiple appearances of the same track then that's really the obvious way of doing it - having the track as an entity in its own right and linking that track to however many different records it appears on, and using the linking table to store information specifically related to that track's relationship with each release (i.e. primarily the track's ordinal position on that particular record). A perfectly sensible approach, if that's what's trying to be achieved.

In third normal form terms, information such as artist and genre do clearly link with track rather than record - and so long as track equates to recording rather than song it's not going to change from one instance of a specific track to another.

On the other hand, clearly an album may have a genre of its own which is separate from the genres of the individual tracks - think of the old "File under..." exhortations that used appear on the backs of LP sleeves. I'm not saying there isn't a (musical rather than database) relationship between the two but equally not every track on an album has to fit a specific genre in order for an album to be considered 'electronica' or 'hip-hop' or 'jangly guitar pop' or whatever.

With artist too, especially when dealing with a desktop database and therefore a limit on processing power, disk IO etc, there may be a certain pragmatism to noting that the artist of a particular album is 'The Beatles' without having to examine the artists linked to each of a dozen tracks every single time you want to find it out.

And its very difficult because the same track title/artist may be released in different versions, live/studio/extended. It can get very difficult designing a schema that fits everything. It may depend whether it's something you are trying to sell, or something for your personal use.

Nowadays artists are harder as well, because you get "featured" artists.

 

[MajP]

Although it goes against a lot of database principles, you could (purely for display purposes) create a temporary table that stored all the concatenated results, then link that back to the main data.

After all this data is very much static once you have created the proper entries. If you made some edits you would need to update the table.
And you would need to add new songs to it when they were added. So it would be a bit of a manually controlled process

I use this idea quite a bit with either a standalone table (not necessarily a temp table) or directly in the main table.
For example I had a main table of Trouble Tickets and another table with Software Change Requests. One or more software changes could relate to a trouble report. In my Trouble Tickets table I had a field called concatenatedSCRs.
I had a procedure that then updates the the field when passed a Trouble Ticket ID. So to be safe I would hit the procedure from multiple touch points. Each time I made an association (add, edit, delete) between a TT and SCR it would update the field. When viewing a TT I would run the code just to be safe and ensure I was in synch. I also ran the code on database shut down. Write the procedure once and call from multiple paths. Works great IMO. Makes continuous form design easy.

Updating a table is a lot faster than a concat function. The concat function on a calculated control in single form view is fine, but used in a query or continuous form is a bad ides. It is a super inefficient process. You end up opening and closing a recordset on each record.
However as @Pat Hartman said if you can limit your records to a small set that the function acts on then you should be OK. In your image above is the calculation done in the query or is it done in the control? It should be in the control so you are not doing it for all records.

 

[lodmark]

The language of the labels makes it difficult for me so I don't understand the relationship between the main form and the subform. All those people didn't play on the Sgt Pepper album. That was a Beatles album but I don't se John, Paul, George, or Ringo anywhere. Are these covers? how/why are they related to a record?

This example is from the film where The Bee Gees was the main artist, and many others performed. The Beatles record wouldn't show you what I mean.

To speed up the concat function, you probably need to modify your form so that it is bound to a query rather than a table and that query has selection criteria to bring back only a single record. I'm assuming that your form is bound to a table and you are using filters to limit the data so the recordset that has to be made is large and every song needs to run the concat function. So instead of 772 * x (#songs per record), the function only needs to run 24 times for this main form record.

It's bound to a query. Query1. But I agree with everything else that you say.
I'm trying to put the concat function in the subform but at this point I'm only getting "#Name?" as the result.
I'm doing it very much like the trial and error method.

Also, you might need additional indexes.

Probably!

Leif

 

[lodmark]

I use this idea quite a bit with either a standalone table (not necessarily a temp table) or directly in the main table.
For example I had a main table of Trouble Tickets and another table with Software Change Requests. One or more software changes could relate to a trouble report. In my Trouble Tickets table I had a field called concatenatedSCRs.
I had a procedure that then updates the the field when passed a Trouble Ticket ID. So to be safe I would hit the procedure from multiple touch points. Each time I made an association (add, edit, delete) between a TT and SCR it would update the field. When viewing a TT I would run the code just to be safe and ensure I was in synch. I also ran the code on database shut down. Write the procedure once and call from multiple paths. Works great IMO. Makes continuous form design easy.

Updating a table is a lot faster than a concat function. The concat function on a calculated control in single form view is fine, but used in a query or continuous form is a bad ides. It is a super inefficient process. You end up opening and closing a recordset on each record.

I'm trying to put the concat function i the filed for artist in the subform.
So far the result is only "#Name?".

However as @Pat Hartman said if you can limit your records to a small set that the function acts on then you should be OK. In your image above is the calculation done in the query or is it done in the control? It should be in the control so you are not doing it for all records.

I've tied this to but then I Only get the limited group as the result and then I can't view all my records.

Leif

 

[lodmark]

My understanding is - a "track" is an audio rendition of some piece of music and is associated with a collection which might be a record, CD, tape, Spotify or whatever. You might have a Record and a CD that are identical as far as tracks go because they come from the same master recording. But when a song is recorded another time, its attributes won't necessarily be identical to the original so it will be a different track even if its attributes are identical to some other record..

One of the problems is that the definition of objects for this project is far from precise.

Going along with @Minty and @MajP, this is one case where duplicating data isn't terrible and could help you a lot in some cases. To do this, you will need to run a one time query to make the table. Then you will need code in the subform that manages artists that updates the track table with the concatenated names. Hopefully, there will be only a single form where this happens so you will have good control over keeping the concatenated names up to snuff. The tract will b the parent form and the artists will be in the subform. In the AfterUpdate event of the subform, you would run the concat function and place the result in a field on the parent form.

This is a interresting way forward, I will try this in some way.

Please NOTE that this duplicate storage is ONLY useful for display. You would NEVER use it for searching. You would always use the tractArtists table for searching.

Thanks everyone!

Leif

 

[MajP]

I've tied this to but then I Only get the limited group as the result and then I can't view all my records.

Can you post a sample. I am not sure I am following where you want to see the concatenated results. Is it the form in thread 29?
Like I said, if there is a need to show this in a large continuous form then you are better off persisting the data in a table. The form in thread 29 only looks like it does show all of your records so this may be a good candidate. If it was single form view, then I would calculate it only in the control, because then you are doing one calculation at a time.

 

[lodmark]

Can you post a sample. I am not sure I am following where you want to see the concatenated results. Is it the form in thread 29?
Like I said, if there is a need to show this in a large continuous form then you are better off persisting the data in a table. The form in thread 29 only looks like it does show all of your records so this may be a good candidate. If it was single form view, then I would calculate it only in the control, because then you are doing one calculation at a time.

I'll post it when I've sorted out all my bad examples.
For now I'm not shure what I've done.

Leif

 

[LarryE]

A Relational database is a hierarchy of data tables starting with the highest data point. When I design one, I always ask myself "when I delete something, what other records down the data hierarchy do I want deleted as well".

In your case, you have various music record label distributors (companies), that distribute music performed by various artists (and co-artists), who recorded songs in various recording sessions. It's a hierarchy of information. Looking at your tables and relationships, I believe you have many tables you don't need.

I would construct the database as follows:

1. A RecordingLabel table comprised of the various record label companies. The table has a Primary key named RecordLabelID. The table holds all the information about the company and any individual employees associated as necessary.
2. An Artist table comprised of all the various artists that recording label represents. It has a Primary key named ArtistID AND a foreign key named RecordLabelID with a 1 to 1 relationship to the RecordingLabel table RecordLabelID field. This table has the following fields: ArtistName, ArtistFacts etc. An artist could be a group or a person.
3. A RecordingSession table comprised of the various sessions by each artist. It has a Primarykey named SessionID AND an foreign key named ArtistID and a 1-1 relationship with the Artist table ArtistID field. Within this table, each data record has the following fields: SessionDate, AlbumName, CoArtists (a person or list of people), Genre, MediaType (CD, Vinyl, etc.). It could hold a DistributionDate field as well and any other information related to this recording session.
4. A Songs Table comprised of all the various songs recorded in each session. It has a Primary key named SongID AND a foreign key named SessionID and a 1-1 relationship with the RecordingSession table SessionID field. Within this table, each data record has, the following fields: SongTitle, TrackNum, TrackLength (in minutes and seconds) and any other data related to the individual song that was recorded.

This structure keeps data about various songs in various sessions by various artists distributed by various recording labels. Only four tables are needed. I don't know if each session has a specific genre or each song has one. You could move the genre field to the Songs table so each song has its own genre if that is necessary.

 

[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