Solved Speed issue with ConcatRelated funktion

[Isaac]

 

[MajP]

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

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

 

[MajP]

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

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

 

[lodmark]

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

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

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

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

Leif

 

[MajP]

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

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

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

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

look forward to the list box that you will add.

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

 

[lodmark]

Looking forward too this @MajP

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

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

Leif

 

[lodmark]

I did not perceive MajP as snotty, however, he was willing to help me.

 

[MajP]

Is there a simple way to auto change it?

The name autocorrect is a powerful feature in Access and can do most of the heavy lifting. You change a field name and it will change in the queries and rowsources/recordsources of form's and controls. I does not change in vba. However, if you write down your changes you can do find and replace throughout the code project. To me it is worth it to spend the time. I try to make my naming convention such that another person could easily figure out what my fields do. I do not do a lot of comments in my code because since my names tend to be long, consistent, readable and descriptive.
I clean up my table and field names all the time. If I come back to a database and have to guess what I was doing, then I know it was not a logical choice. If you wanted to do it, make a backup (good to do all the time anyways) and give it a try. Without knowing your database I would know what a TrackID, AlbumID, TrackID_FK, AlbumID_FK are. I have to think about what is a record versus music and for sure have to think what a record_record_music and record_music_record are. The other reason for me if in the software systems we use (ITunes, MediaMonkey, etc.) the terminology is commone 'tracks' and 'albums'.

I may have asked this before, but do you use "Media Monkey"? If not familiar this is a freeware media system that blows away ITunes. I cannot even use ITunes because MM does everything that you wish ITunes would do and fixes everything you hate about ITunes. The reason I ask is because you could adapt some of the user interface ideas from there. If I got time I will demo some.

 

[MajP]

I did not perceive MajP as snotty

Oh yeah, I was definitely being snotty.

You never told lodmark what caused the problem.

Yeah, I actually did. Not only did I tell him I actually looked at the database and determined the tables that needed to be removed and called them out by name. I also provided the reason why it was read only, a working demo, and a screen shot showing no duplicates.

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

 

[lodmark]

I'm not really sure how to act on this.

But I thought it was wrong of me to go in and change the tables while you @MajP were creating things in the database.
I was afraid that I would then change something that would make the solution you presented not to work.

There's no excuse on my part for not doing anything, it's just an explanation.

Leif

 

[lodmark]

The name autocorrect is a powerful feature in Access and can do most of the heavy lifting. You change a field name and it will change in the queries and rowsources/recordsources of form's and controls. I does not change in vba. However, if you write down your changes you can do find and replace throughout the code project. To me it is worth it to spend the time. I try to make my naming convention such that another person could easily figure out what my fields do. I do not do a lot of comments in my code because since my names tend to be long, consistent, readable and descriptive.
I clean up my table and field names all the time. If I come back to a database and have to guess what I was doing, then I know it was not a logical choice. If you wanted to do it, make a backup (good to do all the time anyways) and give it a try. Without knowing your database I would know what a TrackID, AlbumID, TrackID_FK, AlbumID_FK are. I have to think about what is a record versus music and for sure have to think what a record_record_music and record_music_record are. The other reason for me if in the software systems we use (ITunes, MediaMonkey, etc.) the terminology is commone 'tracks' and 'albums'.

I may have asked this before, but do you use "Media Monkey"? If not familiar this is a freeware media system that blows away ITunes. I cannot even use ITunes because MM does everything that you wish ITunes would do and fixes everything you hate about ITunes. The reason I ask is because you could adapt some of the user interface ideas from there. If I got time I will demo some.

I will definitely test this when or if you have finished the work you are doing with the file now.

No I do not use media Monkey or iTunes either. I'm not an Apple person.
But I will try to watch MM to get inspiration for the layout.

I'll hope you get the time.

Leif

 

[MajP]

But I thought it was wrong of me to go in and change the tables while you @MajP were creating things in the database.
I was afraid that I would then change something that would make the solution you presented not to work.

I am not suggesting you need to change your names, but if you do it is not all that hard. But if you do I would wait until I have given you the demos and you have incorporated them. That way the name autocorrect will update a lot of what I have done. I would wait for now, until there is a good stopping point.

Here is an image of my MM. The treeview is actually multiple treeviews. I you start with Genre then it is
Genre
--Artist
----Album
and then all tracks.
Or you can select
Artist
---Album
or
Album
--album
On the top right you can do a text search and anything that matches comes up

On the right you can select one or more tracks and then edit them on the right click

For the Form I added a button to bring up this form. Then you can add/edit/delete multiple artists.

This program was acting a little unstable so I imported everything into a clean database. That altered some of the background colors. You may want to fix some of the color schemes.

 

[lodmark]

Thanks!
I've done a quick look before i went too work, it looks nice.
I will fix the color schemes, no problems.
Then I will try and use your code and your new "add artist form" on my other forms where I need to add artists.
And of course I will try and change all of the names to be more easy to follow next time i need some help.

I will look in to Media Monkey as well.

Many thanks!

Have a nice weekend.

Leif

 

[MajP]

I played with the MM view, and had some success. Still a work in progress. At ths point you can click on an artist and get all of their albums and tracks. You have all the features on the right to sort and filter each column.

or click on a specific Album. Or even a specific track. If you double click it brings up the details, there you can add edit.

 

[lodmark]

I'm amazed!
You've imported my database into Media Monkey.
I'm really have to look into this further.
Thanks for the hint.

Leif

 

[MajP]

You've imported my database into Media Monkey.

No. I am building a MM like view in Access. As I said, it gave me inspiration for a user interface.

 

[lodmark]

No. I am building a MM like view in Access. As I said, it gave me inspiration for a user interface.

OK, I understand.
Tested MM now and it fetches a lot of information by default.
But I think I like my layout.....

Have a nice weekend.

Leif

 

[MajP]

After playing with this for a while and rethinking it, I personlly think I would do this different. But this is just me talking if I was doing it. I am not recommending you change at this time unless you want to.

I think I would leave a single artist name per "record." So if it is a collaboration then the artist is "Donna Summer and Barbara Streisand" and no junction table. If I did anything the child table would then be to the Artist table. So "Donna Summer and Barbara Streisand" could have child records in the (TblArtists_SubArtists) which would also come from the artist table. So "Donna Summer and Barbara Streisand" would have keys pointing to Donna Summer and Barbara Streisand. You are spending the majority of your time concatenating back together and never real need to look at the records individually. There is little gain in having you junction tblRecords_Artists.

At the track level I would store a string "Bee Gees and Peter Frampton" and not the a reference to the artist table. This way you can type directly into the artist field for any of the "one offs' I have lots of albums where each song is "So and So" ("featuring So and So"). I do not want to store all of these one offs. But maybe would have a hidden field to store the related artist if the artist is in the artist list. But I should still likely be able to join by name.
This is one of those cases where being a little denormalized makes things a lot easier.

 

[MajP]

TreeView demo. Shows a lot of utility, but needs work. The more I played with this the more I would not have junction table on Record_Artist or Music_Artist. "Donna Summers and Barbara Streisand" would be a record in the artist tree. And BS and DS would be there own records. If anything I would have ArtistDetails. The record "BS and DS" would have child records for BS and DS.
I think you will be impressed with how fast you can navigate.

 

[lodmark]

Hi @MajP
I like the tree view.
It gives a different picture of my record collection.
But I wanted to be able to look at the tree view from the record not just from the artist.
It is good, however, that you can choose the forms based on double-clicking on a song or artist.
I think I have to get used to this form.

I tested your latest version of the database.
I tried to enter a new record and it worked fine as long as the form (frm_record2) was open, but the added record was not included when I opened the form again.
This was because the junction table tbl_artist_record was not updated.
So considering what you suggested above, I removed it and let the field "record_artist_ID" in the table tbl_record become FK to "artist_ID" in the artist table. I also deleted the table in qryRecord2 so that it gave the correct result and so that the tables were updated.

I added two records.
The soundtrack to the movie The Woman In Red which has some tracks that are duets and the single Fame with Irene Cara.
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.
In the Soundtrack the order of the songs get little mixed up, A4 got on the top and I can't figure out why?

I changed one more thing. Instead of the button for artist, you can now click in the field to get the form where you can add the artist / s.
Here it would have been nice if you could add an artist who is not in the artist table.

I've tried to change the names but I need a little more time for that.

I'm very happy with what you did.
THANKS!