Solved Speed issue with ConcatRelated funktion

[lodmark]

The remark was not directed at you.

I know!

 

[MajP]

These are just demo concepts. You can do whatever you want with them. These are kind of advanced concepts, so I provide them with caution because if something does not work you may not be able to fix it. The treeview is very useful in my opinion but unfortunately it is not an Access control so it is not seamless.
The treeview is not the most stable thing. I tried to add multiple treeviews and it became untable. So I settle with the two. Here is the album view
This is the ablum-track view

When I get time I will look at the problems you pointed out.

One feature I would add is way to update all tracks in an album. When you select (genre, or date, or artist) to a track I would prompt the user, or have a button, that allows you to assign that property to all tracks on the album. If you play with MM you can select a group of tracks and assign them all the same tag. Something similar. In the above example you could enter your songs. Then select "Pop". It would prompt to add to all songs in album. You could choose only for those not already populated or update all tracks.

 

[MajP]

I recommend at this point you split the database. This way I can help you with the forms and code without stopping you from updating data. Will make version control much easier. When you open the front end (FE) it, it will ask for you to Browse to the location of your backend. Just pick the backend file and it should link.

As long as you do not change your tables then I can just link to another "dummy" copy of the tables. Then I can pass you back a new front end. (code, forms, reports, queries) without messing up your data.

I fixed the record2 form. I made an important fix to the subform.

Once you link, then make a new backend from your current version of the database. Create a new blank database. Import your tables with correct data. Get rid of my backend and link to your backend. If you name it the same as mine it should link automatically.

 

[lodmark]

I fixed the record2 form. I made an important fix to the subform.

Thanks @MajP
What did you fix? I dont see the difference.
If you mean the sort order [qryRecordSubform].[music_ID] that didn't fix it.
I've change it to: [qryRecordSubform].[TrackSort1] & [qryRecordSubform].[TrackSort2] so now it works.

The split is interesting, I've read about it. But I thought I've change all the names first.
But at the same time it would still be possible to change them after the split.

I'm not sure of what you mean with the current version because the current version is the one that you are working on.
I haven't done any adding of record sins I've posted it to the forum.

Leif

 

[MajP]

I'm not sure of what you mean with the current version because the current version is the one that you are working on.
I haven't done any adding of record sins I've posted it to the forum

Although I have tried to be careful, it is very likely I have or will edited the data while testing. So I may not be the same as your good data. The beauty of the Split db is that I can do whatever I want to the data and send the FE back to you without worrying about messing up your data. Plus you can continue editing while I am working.

What did you fix? I dont see the difference.

Test the below, it should be fixed

I noticed that the song disappears from the field in the form until I put the artist there, a little annoying but you get used to it.

I did not see any sort issue problems, because what you describe already appears in the version I had. You are correct you cannot sort
A1, A10, A11, A2, A3...
You do have to sort the disc side A, B, C then the track 1,2,3
But this was working in my version.

The split is interesting, I've read about it. But I thought I've change all the names first.
But at the same time it would still be possible to change them after the split.

It really does not matter, but if you are still working with the table design then you can hold off on splitting the database. But once it gets pretty solid I would split it.

I personally am not so concerned about the names, but I would Strongly reconsider the junction tables for artist_record and artist_music. IMO these add more pain then benefit. In truth, I think logically these junctions are wrong. I would have a single artist "Donna Summers and Barbara Streisand". IMO that is a single "aritst". It is not Donna and Barbara as child records.
If you want child records so you can see the details of each artist then make a child table to artists.
tblIndividualArtist
CombinedArtistID_FK
IndividualArtistID_FK

So if BS is ID of 1, and DS is ID of 7 and "DS and BS" is ID of 15 then you would have values
15 1
15 7
In truth I doubt you would even really need to do something like this.

So I have a collaboration of Alison Kraus and Robert Plant in my tracks. My details on the artist are on the collaboration and not the each artist. I have a single image of both and not two seperate images.

If you do not do it this way you will have to do complex concatenations and searches. Using what you have in the treeview I get seperate albums under BS and DS which is not true. To then do the concatenation in the treeview would be really painful.

Now if you see my form I can search the database by name. So if I search DS then all of Donna's tracks come up as well as collaborations.

 

[lodmark]

Although I have tried to be careful, it is very likely I have or will edited the data while testing. So I may not be the same as your good data. The beauty of the Split db is that I can do whatever I want to the data and send the FE back to you without worrying about messing up your data. Plus you can continue editing while I am working.

Of course, it is best to make a split database. I'll look at this the day I have a database that I'm happy with.

Test the below, it should be fixed

What below?

I did not see any sort issue problems, because what you describe already appears in the version I had. You are correct you cannot sort
A1, A10, A11, A2, A3...
You do have to sort the disc side A, B, C then the track 1,2,3
But this was working in my version.

Yes, and so it is in the query but something changes that. No I've sorted it in the form. It works.

I personally am not so concerned about the names, but I would Strongly reconsider the junction tables for artist_record and artist_music. IMO these add more pain then benefit. In truth, I think logically these junctions are wrong. I would have a single artist "Donna Summers and Barbara Streisand". IMO that is a single "aritst". It is not Donna and Barbara as child records.
If you want child records so you can see the details of each artist then make a child table to artists.
tblIndividualArtist
CombinedArtistID_FK
IndividualArtistID_FK

So if BS is ID of 1, and DS is ID of 7 and "DS and BS" is ID of 15 then you would have values
15 1
15 7
In truth I doubt you would even really need to do something like this.

So I have a collaboration of Alison Kraus and Robert Plant in my tracks. My details on the artist are on the collaboration and not the each artist. I have a single image of both and not two seperate images.
View attachment 94055

If you do not do it this way you will have to do complex concatenations and searches. Using what you have in the treeview I get seperate albums under BS and DS which is not true. To then do the concatenation in the treeview would be really painful.

The junction table for artist - record is gone.
I'm happy with the way that duets, and other songs that have more than one artist, are presented with the solution that you have developed for me, the code presented in # 43.
But I guess that solution presupposes that there is a junction table between artist and music.
How do I resolve this if I do not have the junction table left?
I've discovered that there will be problems with adding entries in other forms in the same way as in frm_record2 if I use junction table between artist and music.

Now if you see my form I can search the database by name. So if I search DS then all of Donna's tracks come up as well as collaborations.

What form? Did you missed to add the file? Or do you mean the split version posted in #78?

Leif

 

[MajP]

What below?

The issue below should be fixed:

I noticed that the song disappears from the field in the form until I put the artist there, a little annoying but you get used to it.

The junction table for artist - record is gone.
I'm happy with the way that duets, and other songs that have more than one artist, are presented with the solution that you have developed for me, the code presented in # 43.
But I guess that solution presupposes that there is a junction table between artist and music.

I am suggesting to get rid of both junctions Record_Artist and Music_Artist. I would have just an Artist table., and tracks and albums reference a single artist. So these are all entered in the artist table
Bee Gees
BeeGees and Peter Frampton
Peter Frampton
BeeGees and Barry Gibb
Barry Gibb

In other words I recommend you go back to your original design. Records and Tracks have a foreign key to tbl_artists.

I'm happy with the way that duets, and other songs that have more than one artist, are presented with the solution that you have developed for me, the code presented in # 43

What I am suggesting is not to use this solution and do away in what I believe is the problem. If you remove the junction table then this solution goes away. It may work, but the design is causing a lot of unnecessary issues.

What I may have is in the track table an additional artist field and I personally would just make it a text. Then you can those one offs like Run DMC walk this way where there is one track on the album with a featured artist.
Track Artist: Run DMC
Additional Artist: (featuring Aerosmith)

The reason I would simply make this text is that I do not think I want the junction table, for something that is the one percent problem. This requires entering a one off featured artist in the artist table, that you are unlikely ever to collect artist picture or facts on. That is my personal opinion.
If a track had a couple of extra artists then could simply do something like
Track Artist: Some Artist
Additional Artists: (SomeOther Artist, Another Artist, And Another Artist)

As I said, If you wanted to can add the child details to the artist table and have tblIndividualArtists.

What form? Did you missed to add the file? Or do you mean the split version posted in #78?

 

[LarryE]

I have been following this discussion for several days and it seems this is becoming way more complicated than it needs to be. ACCESS form controls have their limitations so, I understand why you want to use a tree-view control but my experience is that they are unstable and difficult to implement. You can use continuous forms to achieve the same functionality. All you need to do is add a Selection field to each table with a Yes/No Data Type so it can be selected or unselected. Then, insert each form on a single main form and arrange them so you can view all records in one place. Then use filtering code to filter each form based upon what records are selected. So if you wanted to show all records related to the Swedish group ABBA (one of my favorite groups), you could just search to find it or scroll down to it, then select it and filter the records related to ABBA. See below:

I have taken the liberty of designing a sample using your table data. I have only 4 tables and 5 forms. Media and Genre are characteristics (fields) of the Record Sessions table so those carry through to each song associated with each record, artist and label. I used the power of ACCESS and enforced referential integrity so ACCESS does most of the work. I did code filtering, searching and adding and deleting records, but there is not a lot of code needed. I attach the example.

 

[MajP]

several days and it seems this is becoming way more complicated than it needs to be. ACCESS form controls have their limitations so, I understand why you want to use a tree-view control but my experience is that they are unstable and difficult to implement. You can use continuous forms to achieve the same functionality

To be clear these are just concepts and as I said I probably do not recommed to use a Treeview unless you really wanted to. I hope I made that clear. There are lots of ways to do this.

These are just demo concepts. You can do whatever you want with them. These are kind of advanced concepts, so I provide them with caution because if something does not work you may not be able to fix it. The treeview is very useful in my opinion but unfortunately it is not an Access control so it is not seamless.
The treeview is not the most stable thing. I tried to add multiple treeviews and it became untable. So I settle with the two. Here is the album view
This is the ablum-track view

The concept is a continuous form and a robust means to filter that not necessarily a treeview. I would still go with the datasheet view that provides the sort and filter you want. I would probably replace the tree with listboxes to provide the same functionality just not as aesthetically pleasing.

The complexity is with the best way to do the table structure. There is not one way to do it, but there are ramifications depending on how you choose to organize the data. The problem IMO comes from the fact that a track may or may not be related to an album. An album artist can be different from the track artist. Such as a compilation or guest performers on a track.

 

[MajP]

Same idea without a Treeview. I still feel this is a very simple and clean interface. Instead of a tree view then you can use listboxes. Since these list are large I would give them find as you type capability.
So you can select and filter by artist or album and you can cascade. If you select artist you get the albums for that artist.

Once you pick an artist you filter the albums by that artist. Sam idea but slightly different user experience.

Then you can pick by album.

 

[lodmark]

Hi @MajP

Tonight I've noticed that my original tables are more complete then the one that you split.
Exactly as you pointed out.
This I'must do something about.
Can I split my original database and use that back end with your front end?
I've tried that but it doesn't seem to work, I'm getting error messages.

I've followed what you been writing and I will definitely try what you are suggesting about loosing the artist-music junction table.
But for now I want to try and add functionality to the record form with code for adding artist that's not in the list.
I've done some work on it but it's not complete.

I've also done som work with adding your code to frm_program, but there is still a lot to be done, and the file is on a computer that I don't have access to tonight. I will post that file later, after I've done som more work on it.

The issue below should be fixed:

Understood.

I am suggesting to get rid of both junctions Record_Artist and Music_Artist. I would have just an Artist table., and tracks and albums reference a single artist. So these are all entered in the artist table
Bee Gees
BeeGees and Peter Frampton
Peter Frampton
BeeGees and Barry Gibb
Barry Gibb

In other words I recommend you go back to your original design. Records and Tracks have a foreign key to tbl_artists.

That will stir up my artist table as I want only one artist per post in that table. With the suggestion above the songs that are done as duetts or with guest performers will show up in it's own post as Peter Frampton and BeeGees and not together with each artist. My goal is that the song shows up together with other songs dona by The BeeGees and also together with Peter Frampton. Today I don't know how I will tell that the song is a duet in that form but it will come to me.

What I am suggesting is not to use this solution and do away in what I believe is the problem. If you remove the junction table then this solution goes away. It may work, but the design is causing a lot of unnecessary issues.

What I may have is in the track table an additional artist field and I personally would just make it a text. Then you can those one offs like Run DMC walk this way where there is one track on the album with a featured artist.
Track Artist: Run DMC
Additional Artist: (featuring Aerosmith)

The reason I would simply make this text is that I do not think I want the junction table, for something that is the one percent problem. This requires entering a one off featured artist in the artist table, that you are unlikely ever to collect artist picture or facts on. That is my personal opinion.
If a track had a couple of extra artists then could simply do something like
Track Artist: Some Artist
Additional Artists: (SomeOther Artist, Another Artist, And Another Artist)

As I said, If you wanted to can add the child details to the artist table and have tblIndividualArtists.

This I will try sometimes....

View attachment 94065

Understood.

Goodnight!
Leif

 

[lodmark]

Hi @MajP
I have now done as you suggest in your posts 82 and beyond. I have created a new database with other types of connections than in my old one.
In this way, I have managed to achieve both showing duets in a simpler way and also records with various artists.
However, I have not yet managed to find a way to avoid entering anything in the field for featured artist, now I make a space there if the song lacks a featured artist.
I would like to receive your views on how I have succeeded or not with this database.
I've tried to write most of it in English.

Leif

 

[MajP]

This is my thought and it is debatable. Other people may have different opinions what will work well. I need to look at my data to see what percentage of time albums have additional artists, and tracks have additional artists. My guess it is a 1% issue.
For the album I would enter every artist in the artist table like you have it That means there is a related record. If it is Donna Summers and Barbara Streisand I am entering that as a single artist. I am not doing a junction table for this rare case. I do not think you would gain anything from that. You are not tracking about of artist information except their name. For the featured artists that is going to be a text field and not a related field. Here is why, it has to be one or the other. You either have to have a junction table or you need free text. You need free text for a way to show more than one featured artist artists. This featured fields are so unlikely to be searched, sorted etc. If you do an you want every instance then incorporate a text based search. That is how all the track management software does it. There is really no relational data. All tags are at the track level. It is repetitive (so that they can stand on their own) but works fine.

 

[lodmark]

Hm! Thanks @MajP
Actually I don't know why I did the featured artist field on the album, probably because I then already was thinking of the tracks, so that one goes away immediately. As you say its a 1%.
I will try what you suggest and add a featured artist as a text field instead, that will be version 2.
My quest continuous.
Leif

 

[lodmark]

@MajP
I've started with the work......where do I put the order of the track on the record?
Example: The song Guilty is both on a LP and on a singel, it happens that it's the first track on both, A1.
But the backside of the singel (B1) is a song called Life Story and that one is the second track on the second side of the LP, B2.
How do I solve this without the junction table and without adding the song twice in the tracks table?
Leif

 

[MajP]

How do I solve this without the junction table and without adding the song twice in the tracks table?

Sorry for the confusion. The only junction tables I thought I would not include are AlbumArtist and TrackArtist. This junction table is needed.

 

[lodmark]

Puh! @MajP Then I can go on with your thoughts about the free text fields.
Leif

 

[EzGoingKev]

@MajP - I found this thread after trying to use ConcateRelated on a large data set.

I downloaded a few different versions of the db you posted on here. I tried to run the query "qry_artist_DConcate" so I could see how your code worked. I get #ERR3078.

Any ideas on this?

 

[murphybridget]

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.

An album can definitely have a cohesive genre or vibe that ties it together as a whole, even if the individual tracks vary widely in style. The overall theme, production style, or artistic vision can create a unified feel that defines the album's genre, much like how LPs used to be categorized. It's the combination of all the elements that gives an album its unique identity.