Solved Problems with duets! (1 Viewer)

lodmark

Member
Local time
Today, 11:22
Joined
Jul 24, 2020
Messages
232
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 so.
In my form which present the artist, I want to be able to see both which records I have with the artist but also which songs I have used in my program, but where I do not have the record.
I guess I need an intermediate table between Artist and record like below.
Skärmklipp.PNG

My thoughts is that i will populate this table like this, where artist_ID is from the artist table and record_artist_ID is the artist_ID of the song.
Skärmklipp.PNG

But I'm missing something here I think?
Leif
 

Attachments

  • Skivsamlingen - copy.zip
    600.4 KB · Views: 479

Dreamweaver

Well-known member
Local time
Today, 10:22
Joined
Nov 28, 2005
Messages
2,466
You didn't apply what I suggested I.E. the artistID and recordID should be keys the primary you have should be a autonumber indexed to no dups.

Also you really need to be using referential integrity.

You really need to read up on tables and relationships .
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:22
Joined
Feb 28, 2001
Messages
26,996
The way I did this for my list of music CDs is I associated many names to each CD through a junction table that linked person to CD. BUT in the junction table, I had another field that pointed to a "role" table that said "Singer" or "Guitarist" or "Drummer" or ... whatever. I was even able to note producers, songwriters, lyricists, cover artists, and even (for classical stuff) conductor and orchestra name.

This way, if you have a duet you have two singers. If you have a trio identified separately, you have three singers. OR if you have a small named band, you can reference them by band name.
 

Dreamweaver

Well-known member
Local time
Today, 10:22
Joined
Nov 28, 2005
Messages
2,466

lodmark

Member
Local time
Today, 11:22
Joined
Jul 24, 2020
Messages
232
This topic relates to
https://www.access-programmers.co.u...ecord-collection-database.313932/post-1731723

I use A Contibution combo field with a FK To another table tblcontributions I added a picture of a system I'm building but didn't include it as the OP could have seen it plus it could have complicated the issue I do think it better to have people thnking for themselfs rather than do it allfor them .
I'm sorry @MickJav
Of course you are right.
And it was certainly not right of me to seek help again as I did not really understand what you meant.
Or, as you yourself suggest, I should think a little myself. Maybe this problem is a little too big for me.
The help I got earlier from @arnelgp , @theDBguy and @MajP to name a few has been fantastic and I'm grateful for that.
I'm really trying to figure out how to do it on my own. But I do not succeed or I have too little patience and want to get started with registering my records.
I will try to add these last examples you gave me so let's see what that could give me.

Maybe it's the case that this forum is not meant to help us who want but can not?
Again, I'm sorry to have taken up your time.

Leif
 

lodmark

Member
Local time
Today, 11:22
Joined
Jul 24, 2020
Messages
232
You didn't apply what I suggested I.E. the artistID and recordID should be keys the primary you have should be a autonumber indexed to no dups.

Also you really need to be using referential integrity.

You really need to read up on tables and relationships .
But I think I've done that. They are keys and teh primary is autonaumber and indexed to no duplicates.
I've added referential integrity between the tables artist and the middle table artist_record now.
The reason that there is no referential integrity between the middle table artist_record and record is that I can't create it.
Probably because the middle table don't have all the necessary entries in the table.
Skärmklipp.PNG

I am going through the table artist and adding items in the middle table when I found those with multiple artist in the same row.
If the don't already exist I divides them into two separate entries. I then add them in the middle table on two rows.
Skärmklipp.PNG

That's how I figured out how to do this.
And I've really try too think! :unsure:

Leif
 

Dreamweaver

Well-known member
Local time
Today, 10:22
Joined
Nov 28, 2005
Messages
2,466
You have records In tbl_Records That are not in tbl_Artists_Records You need to create an Append Query With Record_artistsID and recordID from tbl_Records so you can fill tbl_Artists_Records with raeted data.

You can remove the artist ID from tbl_Records as the info for a recording can be obtained from the new table, But only when you are sure you have it correct but make a backup!!!!
 

lodmark

Member
Local time
Today, 11:22
Joined
Jul 24, 2020
Messages
232
You have records In tbl_Records That are not in tbl_Artists_Records You need to create an Append Query With Record_artistsID and recordID from tbl_Records so you can fill tbl_Artists_Records with raeted data.

You can remove the artist ID from tbl_Records as the info for a recording can be obtained from the new table, But only when you are sure you have it correct but make a backup!!!!
Yes I have to do the basic work.
But the artist table contains many entries that today consist of two or more artists because I added them in that way. Do I not have to first divide them into separate entries?
I also do not want to destroy anything in the relationships to the other tables in the database.
Thanks for helping me!
Leif
 

Dreamweaver

Well-known member
Local time
Today, 10:22
Joined
Nov 28, 2005
Messages
2,466
Wait till you get to 68k artists and 240k Recordings It will soon tell you if you messed up ;)
 

mike60smart

Registered User.
Local time
Today, 10:22
Joined
Aug 6, 2017
Messages
1,899
Lief

Can you fill in what the Record Title should be for the records in your Junction tbl_artist_record ?

IDartist_IDRecordIDRecord Title
17676
222076
312021211
412121211
5193196
6196196
 

mike60smart

Registered User.
Local time
Today, 10:22
Joined
Aug 6, 2017
Messages
1,899
Reason you are having problems is because RecordID in the Junction table should be joined on RecordID Autonumber in tblRecords
 

lodmark

Member
Local time
Today, 11:22
Joined
Jul 24, 2020
Messages
232
Lief

Can you fill in what the Record Title should be for the records in your Junction tbl_artist_record ?

IDartist_IDRecord_artist_IDSong Title
17676You're The One That I Want
222076You're The One That I Want
312021211When I'm Sixty-four
412121211When I'm Sixty-four
5193196No Moore Tears Enough Is Enough
6196196No Moore Tears Enough Is Enough
Not really! 76 in the list above is not the RecordID that's the artistID for the song. This specifik song is "You're The One That I Want" with Olivia Newton John (220) and John Travolta (76). The record Grease is not yet in the table/database.
This is because the database that I've started with contained songs that I've used in my radioprogram inspired by my record collection.
The way I've set up my tables and the relationships between them determines that the junction table is placed between the artist table and the record table.
So far this is the only entries in the junction table.
I have some work to do to add more records so that I can set up the relationships thoroughly.
Now it's bedtime for me after watching Sweden got beat by France in football. :mad:

Leif
 

Dreamweaver

Well-known member
Local time
Today, 10:22
Joined
Nov 28, 2005
Messages
2,466
Reason you are having problems is because RecordID in the Junction table should be joined on RecordID Autonumber in tblRecords
I agree with @mile60smart Didn't notice it I used 3 field in my explanation I've attached example
 

Attachments

  • Many To Many.zip
    26.7 KB · Views: 477

lodmark

Member
Local time
Today, 11:22
Joined
Jul 24, 2020
Messages
232
Thanks for all the input!
Questions!
In my artist table I have a lot of entries that holds both artists in a duett, like Olivia Newton John and John Travolta that relates to the song "You're The One That I Want". This is just one example of over a hundred others. I was manually trying to separate the two artists in two entries and then use the junction table to get both together again to the sam song. Is this the wrong way to do this? Don't I have to separate all the artists that are two ore more in the same post in the artist table first, before I can use a append query to fill the junction table?
Otherwise I don't gain anything with the junction table, I think.

Leif
 

lodmark

Member
Local time
Today, 11:22
Joined
Jul 24, 2020
Messages
232
I agree with @mile60smart Didn't notice it I used 3 field in my explanation I've attached example
Thanks both for your suggestions.
What you suggest @mike60smart may be a great solution but as I tried to answer above, the reason I have the link to another field in the records table (record_artist_ID) is that not all songs are on records and that the song - artist link is stronger in my database than the link artist - record. This is because the database was originally based on the records I played in my radio programs.
If I were to connect to record_ID, I would not be able to see the songs that are not in the records table.
Or am I thinking completely wrong here?
See also my question above.
Leif
 

mike60smart

Registered User.
Local time
Today, 10:22
Joined
Aug 6, 2017
Messages
1,899
Hi Leif

Is this what you need to see when selecting an artist?
 

Attachments

  • Artists.JPG
    Artists.JPG
    46.5 KB · Views: 469

lodmark

Member
Local time
Today, 11:22
Joined
Jul 24, 2020
Messages
232
Something like that!
This is the form that I'm using for the artist.
Skärmklipp.PNG


It has two sub forms, the first shows the records of the artist that I have in my record collection and the second one shows the songs that I have in the database with this artist. The second one can show songs that I played in my radio show but wich is not in my record collection, my plan is that these songs should be marken i the field for media with "SP" for Spotify.
But that is just thoughts for the future.
The second song in the list "Guilty med Barbara Streisand" is a duet. And her i think it would be nice if I could search for Barbara Streisand and se that she also participated in the song. That isn't possible in the way that the database is made today.
Another thing is that when i pick the song for a radio program or if it's in a compilation record I want the artist to be seen on the artist line, like this:
Skärmklipp.PNG

Or this for the record:
Skärmklipp2.PNG
I've added Barbara Streisand to Bee Gees temporarily just to show what I mean. And misspelled it. 😫
So this i what I try to accomplish.

Leif
 

Users who are viewing this thread

Top Bottom