Solved Speed issue with ConcatRelated funktion

[lodmark]

Hi, I'm back!
Are there any solutions to the problems of slow response when asking questions to large tables with Alen Brunee's ConcatRelate function?

Leif

 

[Ranman256]

Ive never used (needed) ConcatRelate(), so I would advice against using it.

 

[Gasman]

You should be asking questions of your data first, then use ConcatRelated ?

 

[jdraw]

lodmark,
Can you tell us more of the context and requirement? There may be options.

 

[lodmark]

OK, I'll try to describe what I want to accomplish.
At the end of 2020, I had two threads running about this.

Solved - Problems with duets!

When I started asking questions in this forum about how to create functionality in my database, I got a question about how I had intended to do with duets, ie songs with two artists. I then replied that I had intended to write the song with both artists. It now turns out to be a bad idea to do...

www.access-programmers.co.uk

and https://www.access-programmers.co.uk/forums/threads/duets-or-songs-that-have-several-artists.315102/
Since then, I have thought about different solutions and tested.
I have added an intermediate table in my original relationship image, tbl_artist_music.

Which allows me to create multiple artists on the same song.
The problem at the end of 2020 was that I placed this table incorrectly, between tbl_artist and tbl_record.
Now it is between tbl_artist and tbl_music (which contains the songs) I know that the names on the tables are a bit misleading which makes it difficult to follow.
I tested the solution with a few records with good results, nice and clean with several artists on the line for the song on a record.
But with a table that contains more than 3000 songs and is constantly increasing, this solution is not possible.
My hope is now returned to this group's knowledge and skills.
I enclose the database, unfortunately in Swedish.
Leif

 

[cheekybuddha]

Hi,

Unfortunately your db is in too new a version of Access for me to open, but have you considered using as listbox to display the artists.

You should be able to use tbl_music.musicID as the ControlSource, and something like the following as the RowSource:

SELECT
  a.artist_name
FROM tbl_artist a
INNER JOIN tbl_artist_music am
        ON a.artist_ID = am.artist_ID
WHERE am.music_ID = [music_ID];

(nb untested)

 

[cheekybuddha]

Also, artist in the music table should actually be the songwriter rather than the performer although they could be the same person.

Songwriter too should be a junction table since a song can often be written by more than one person

 

[lodmark]

Thank you for all your input.
And thank you @Pat Hartman You are quite right in pointing out that you gave me the solution already last year.
But either I did not understand it or it meant too great a change in what I, with your fantastic help, have already achieved.
I may have been stuck in an appearance on a form where I want the artists who make the song to be visible.

As for what you write about the songwriters, I have not intended to include these in the database, yet ....
The database contains my record collection but is also the basis for the content of my radio programs.
I have had a hard time finding time to work with the database during the spring, but this summer I found this picture of a relationship online.

That's when I got the idea of where to place the junction table.

I think maybe I need to start over from the beginning by creating a database that will be so good that it meets my small needs.
What you write @Pat Hartman that I can only have one artist per record is not correct, see the picture below.
But the solution in my existing database may not be perfect.

I continue to be grateful for all the tips.
Leif

 

[lodmark]

I have tried to create junction tables myself between the tables that had a connection to ID fields.
I then deleted these ID fields, record_artist_ID, music_artist_ID and music_artist2_ID.
It created some errors in the forms but these I am trying to fix.
Just hope I can save new records.
I tried to normalize the tables with access own tool but found no way to split the tables but referred to manual processing.
Curious about your point of view on the new relationship scheme @Pat Hartman, what more can be done?

 

[lodmark]

Thanks for your reply @Pat Hartman
First of all, I would like to apologize again for the names on my tables are so misleading.
Now take this in the right way, when it comes to vinyl records I know what I am doing.
@Pat Hartman as you write in your first comment you seem to have misunderstood what my database is supposed to contain. Today there is nothing about the songwriters or sheet music in my database.
But I am very grateful for your comments that I will try to apply to my database.

Here is an explanation of the tables.

• tbl_artist contains the artists who perform the songs, not those who have written the songs, even if it is sometimes the same person / s.
• tbl_music contains the songs, not the notes ie. what you call sheet music. Each song has a medium that can be an LP, a 45 or a 12 or maybe a CD or what I call SP ie Spotify and it is because I in my radio programs are sometimes forced to include songs that I do not have in my record collection. Had I not had the media in this table, I would not have known what the songs had for the media.
• tbl_record contains my records, not much to add there.
• tbl_program is a list of programs I made.
• tbl_program_date contains the date the programs were sent.
• tbl_program_music creates a connection between programs and songs in the programs.
• tbl_genre is of course the genre and is found like media on both records and songs.
• tbl_record_label contains the record companies.
• tbl_music_record creates a connection between the disc and the song.
• tbl_artist_music creates a connection between the artist and the song. (New)
• Finally, the table tbl_artist_record creates a connection between the artist and the record. (New)

When it comes to what you write about auto numbers (comment 2), it is completely correct and I should remove those that are not the primary key.
I do not agree with what you say in your comment no. 7. As I wrote earlier in this post, my programs can sometimes contain songs from Spotify, but sometimes also all songs from one and the same album.
I have made all the code work for the display in the forms but not to save new records. I have also not solved my original problem of getting the artists who do one and the same song to be shown on the same line.
Why is it so difficult? After all, it's just ones and zeros.
Leif

 

[lodmark]

Sorry to hear that @Pat Hartman
But still, with my new explanation of what the tables contain, I would like to see how you would make a correct schedule.
Maybe that would make me see my mistakes more clearly.
After that, maybe I can move on with my basic problem.
Leif

 

[lodmark]

Sorry but the headache is still here.
You sad that - "Your separation of "music" from "record" makes no sense to me."
I understand that it was a mistake to name the table containing the tracks on the records for tbl_music.
And it was also a mistake to write song instead of tracks in my earlier respond.
But that is what I did and so it will be.

I realize there are more compilations than duets, but I have to start somewhere. And I have created a junction table between artist and record as well as between artist and music (tracks) to make it possible to have more then one artist on each tracks or record.

I Still would like to se a scheme over how you would put the tables together.
Meanwhile I've change mine and perhaps it is now easier to follow.

I'm sorry we can not reach out to each other, maybe it's because of my poor English and because I explain poorly. But I'm really trying to understand what you mean.
Just as you have pointed out, I do not understand relational databases even though I have worked with them for almost 40 years but on a hobby level.
Before we end up in a situation where it becomes unpleasant, I think we draw a line over this discussion and I have to turn my questions elsewhere.
It has been nice here and I have received a lot of help, thank you for that.
Farewell
Leif

 

[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

 

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

 

[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