Solved Speed issue with ConcatRelated funktion (2 Viewers)

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

 

[Pat Hartman]

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

The remark was not directed at you.

 

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