Solved Speed issue with ConcatRelated funktion (1 Viewer)

[Simon_C]

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.

 

[lodmark]

You have too many relationships and they are still not logical. Data must be stored at the lowest level where you need it. So if you want artist by track, it is not necessary to connect artists to a record and doing so violates normal forms since it leaves the data subject to anomalies. You can derive artists for a record by summarizing the artists by track.

I understand and have now removed that relationship.
Unfortunately, it is the case that some of the songs I play in my programs are not available as records in my record collection but are taken from Spotify and in some cases from Youtube.
So the relationship with a record is not self-evident.
What do you think is the best thing to do?
I used the table tbl_artist_music to create a many to many relationship between artist and song that I could then use with Allen Brone's ConcatRelated function.
I understand that his function is not the best when it comes to large tables.
If I can fix my database as you suggest. Do you think I can use his feature anyway, or is there a better way to get a presentation of multiple artists doing the same song?

media has noting to do with track. It is related to the physical record. There is a case for relating genre to track but the relationship is the same as for artists. If you put genre at the track level, you summarize it to get genre by record. Most records don't have different genre for different tracks although there is certainly some variability there. So again, if you keep genre by track, you should NOT keep it by record.

I thought I wanted media on track too because I could then distinguish between songs from my record collection and songs taken from Spotify or Youtube.
On the other hand, the songs on a record can be of different genres, rock, disco, reggae, etc. For example on a compilation disc.
But of course you could remove genre from the album.

Also, I'm not sure that track is an entity in and of itself. You don't buy a track, you buy a record so track doesn't stand alone. That means that there is not a many-to-many relationship between record and track since a track only exists on a record. The relationship is 1-m and so you don't need the junction table. It's like defining a piano key as an entity. That makes sense if you are creating a parts database and building a bill of material for a piano but not for any other reason. A piano key does not stand alone. It is useless unless attached to a piano.

If the same song occurs on multiple records and the track data is identical or at least similar, you can create a copy function to reduce your data entry task.

The same track can appear on several discs.
For example, I have several singles in different editions and then there are compilations.
It is difficult to do this in the right way.

All my best.
Leif

 

[lodmark]

Hi Pat, I'm glad you still want to answer me.
I have the greatest respect for your knowledge and experience.
I agree that much of the job is to plan how the different parts of the database should be connected and in that case I have failed. But maybe you can save some of what is already stored?
I feel like I want to tell you about how my desire to make a database came about.
It all started with me making radio programs with Discomusik from the 70s and 80s, based on my own record collection.
To keep a little track, I then made a list in Excel of which songs were played in each program, the list contained the song name, the artist and the date the program was broadcast.
When I started thinking about creating a database, the list contained about 1700 lines.
Based on that list, it was relatively easy to create a database with a connection between the artist and the song.
My ambitions, however, were to catalog my record collection but with the Excel list as a basis.
There you may have some explanation for why it looks the way it does.

Leif

 

[Pat Hartman]

Unfortunately, it is the case that some of the songs I play in my programs are not available as records in my record collection but are taken from Spotify and in some cases from Youtube.

Then make YouTube or Spotify a "record". A "record" then becomes a collection of tracks from one source. You probably want to make YouTube and Spotify options for Media also to facilitate searching. I understand the confusion between what is defined for a track and what is defined for a record now that I know that not everything actually comes from some type of physical media. This also emphasizes the importance of attaching attributes at the lowest level. Things like media are associated with the record but the artist is associated with the track since different tracks on the same record can be performed by different artists. So use that concept to decide what attributes go where. It makes the data entry more cumbersome to put more attributes at the track level but it does eliminate the confusion and having the data in a single place eliminates the creation of anomalies which would be conflicts between what the record says and what the tracks say.

You also may be trying to capture too much information. Searching is important though and without the details of a tract, you won't be able to pull up a set of tracts that you want to turn into a program.

If you had a sheet music table as I thought tract was initially, that would enable you to eliminate some of the duplication of tracts. A tract would point to the music table and that would ensure that the information about any particular tract would be as standardized as possible. I would advise this new table even if you don't also start collecting information about the authors of a song. There is still stuff that can't go into the music table such as the length of the song. Maybe every time Elvis sings the same song on a different record, he uses the same arrangement so the length remains constant but I doubt it and since you are producing programs, time is a critical element and has to be accurate. So even though 9 times the time is 2:58, the 10th time it is 3:04 so you simply can't put time in the music table. Maybe it makes sense to use the combination of music and arrangement so you can have two records for the same song. One that is 2:58 and the other that is 3:04. That way instead of entering the same data 10 times, you enter it twice and the track points to the musicArangement table. Think about how this concept works. It is only slightly different from what you have now and might be closer to what you had originally. I didn't understand the original layout because of the duplication and the way things were named. This gives you a way to separate the physical attributes of implementation as a tract from the basic description of a piece of music. Having once been a teenager, I know a lot about music and recordings but I've never had to design a database to track all the stuff you are trying to track.

Having your data currently in a schema that isn't correct just leads to more problems as you try to develop new features. But I do understand the difficulty of converting it. Been there, done that for a number of applications I had to "fix".

Things like the concatenation function that initiated this question are nice to have but perhaps not actually critical. For your own purposes, you can probably just use subforms and subreports. The only time you would actually need to do this concatenation is if you wanted to export a flat file to Excel to send to someone else. In that case, you would want the artists to all be strung together in a single field. But for all day-to-day operations of the application, the artists should be stored in properly normalized tables. One thing you could play with for reports is making the artists' subreport multi-column. Pick a number like 5 for the number of columns. That way, the artists would only go to a second row if there were more than 5 attached. This won't work for forms but is quite useful for reports and might actually eliminate the need for the concatenation entirely.

 

[lodmark]

Then make YouTube or Spotify a "record". A "record" then becomes a collection of tracks from one source.

Did not think so far, it was wise.

You probably want to make YouTube and Spotify options for Media also to facilitate searching.

It is already done.

I understand the confusion between what is defined for a track and what is defined for a record now that I know that not everything actually comes from some type of physical media. This also emphasizes the importance of attaching attributes at the lowest level. Things like media are associated with the record but the artist is associated with the track since different tracks on the same record can be performed by different artists. So use that concept to decide what attributes go where. It makes the data entry more cumbersome to put more attributes at the track level but it does eliminate the confusion and having the data in a single place eliminates the creation of anomalies which would be conflicts between what the record says and what the tracks say.

I will keep that in mind.

You also may be trying to capture too much information. Searching is important though and without the details of a tract, you won't be able to pull up a set of tracts that you want to turn into a program.

If you had a sheet music table as I thought tract was initially, that would enable you to eliminate some of the duplication of tracts.

Sheet music will probably never be a subject that I will work with because I am not a musician but "just" a DJ.

A tract would point to the music table and that would ensure that the information about any particular tract would be as standardized as possible. I would advise this new table even if you don't also start collecting information about the authors of a song. There is still stuff that can't go into the music table such as the length of the song. Maybe every time Elvis sings the same song on a different record, he uses the same arrangement so the length remains constant but I doubt it and since you are producing programs, time is a critical element and has to be accurate. So even though 9 times the time is 2:58, the 10th time it is 3:04 so you simply can't put time in the music table. Maybe it makes sense to use the combination of music and arrangement so you can have two records for the same song. One that is 2:58 and the other that is 3:04. That way instead of entering the same data 10 times, you enter it twice and the track points to the musicArangement table.

There are songs in my record collection, e.g. 7 "and 12" where the songs have different lengths. But I have also chosen to index the image of the record because I sometimes have different editions of the same 7 ".

Think about how this concept works. It is only slightly different from what you have now and might be closer to what you had originally. I didn't understand the original layout because of the duplication and the way things were named. This gives you a way to separate the physical attributes of implementation as a tract from the basic description of a piece of music. Having once been a teenager, I know a lot about music and recordings but I've never had to design a database to track all the stuff you are trying to track.

Having your data currently in a schema that isn't correct just leads to more problems as you try to develop new features. But I do understand the difficulty of converting it. Been there, done that for a number of applications I had to "fix".

Things like the concatenation function that initiated this question are nice to have but perhaps not actually critical. For your own purposes, you can probably just use subforms and subreports. The only time you would actually need to do this concatenation is if you wanted to export a flat file to Excel to send to someone else. In that case, you would want the artists to all be strung together in a single field. But for all day-to-day operations of the application, the artists should be stored in properly normalized tables. One thing you could play with for reports is making the artists' subreport multi-column. Pick a number like 5 for the number of columns. That way, the artists would only go to a second row if there were more than 5 attached. This won't work for forms but is quite useful for reports and might actually eliminate the need for the concatenation entirely.

I have started to think of different alternatives to a presentation in a form by several artists on the same song.
But somewhere I need to connect the artists with the song and I have not got it together yet.

Thanks @Pat Hartman , your thoughts make me move forward.

 

[Pat Hartman]

Sheet music will probably never be a subject that I will work with because I am not a musician but "just" a DJ.

I didn't say you needed it because you cared about the sheet music but it is a logical grouping place which could minimize your data entry at the track level as I explained later.

I need to connect the artists with the song and I have not got it together yet.

On a form, you would use a subform because you need to allow edits. On a report, you could use a listbox because it is more compact if you don't want to use the multi-column subreport that I suggested earlier.

 

[lodmark]

On a form, you would use a subform because you need to allow edits. On a report, you could use a listbox because it is more compact if you don't want to use the multi-column subreport that I suggested earlier.

Hi @Pat Hartman
This is what it looks like when I look at a vinyl record in my record collection in the database.
This is the soundtrack to Sgt. Peppers where the Bee Gees did many songs together with other artists.
The first song with Bee Gees and Paul Nicholas is a new entry in the artist's table, I wish I had just added Paul Nicholas and then paired them together in an intelligent way to get the same result as in the picture.
I understand that I need a subform to get several artists presented on the same song, but I do not see how it can be done in a nice way that is similar to what I have today.
There are more forms that present the songs in a similar way. For example, the Artist form where all records and individual songs made by the artist are visible.
Anyone have any good tips on how this can be done?
Leif

 

[Minty]

Hi @Pat Hartman
The first song with Bee Gees and Paul Nicholas is a new entry in the artist's table, I wish I had just added Paul Nicholas and then paired them together in an intelligent way to get the same result as in the picture.

Leif

This is the scenario @Pat Hartman was eluding to. When you set up the Data your tbl_SongArtist needs to be a child table to the song.

So in your Data the Song would have two table records in tbl_SongArtist, one for Paul Nicholas and one for Bee Gees.
You can display it however you like including using the concatrelated function, but it's a child table.
The Bee Gees & Paul Nicholas are not an artist.

 

[lodmark]

This is the scenario @Pat Hartman was eluding to. When you set up the Data your tbl_SongArtist needs to be a child table to the song.

So in your Data the Song would have two table records in tbl_SongArtist, one for Paul Nicholas and one for Bee Gees.
You can display it however you like including using the concatrelated function, but it's a child table.
The Bee Gees & Paul Nicholas are not an artist.

Hi @Minty
Of course, The Bee Gees & Paul Nicholas is not one artist. This is what I want to be able to present in a good way.
This is what happened when I added the song because I did not have an alternative then.

Everyone who has helped me wants the same thing, but I probably do not understand what you mean.
I have not received an example that I can understand and continuing to work with.

I consider my database relationship schedule to be as good as it can right now.
Allen Brown's ConcatRelated function is good and does what I want but is far too slow.

The Bee Gees of course have their own post in the tbl_artist, this song with Paul Nicholas was the first with him.
I have now added Paul Nicholas as a post.
In the junction table between artist and song, there is the song with twice one for each artist.
Then it looks like this. But as i said much too slow when I click next post.

Leif

 

[Minty]

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.

Alternatively, have you looked at the @theDBguy simple CSV function? http://www.accessmvp.com/thedbguy/codes.php?title=simplecsv
I don't know if it is more efficient than Allen Browne's function.

Finally - and it's a bit of a large step, but if you installed SQL Server Express as your back end database, the later versions have this functionality inbuilt, and it is blindingly fast.
String Aggregate function - https://datamajor.net/mssqlconcatrows/#1-concatenate-rows-using-string_agg

 

[Pat Hartman]

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?

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.

Also, you might need additional indexes.

 

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

 

[Pat Hartman]

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.

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.

 

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