Solved Problems with duets!

[lodmark]

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.

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.

But I'm missing something here I think?
Leif

 

[Dreamweaver]

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]

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]

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 .

 

[lodmark]

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]

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.

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.

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

Leif

 

[Dreamweaver]

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]

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]

Wait till you get to 68k artists and 240k Recordings It will soon tell you if you messed up

 

[lodmark]

Wait till you get to 68k artists and 240k Recordings It will soon tell you if you messed up

That's just therefore I have to get it right now!

 

[mike60smart]

Lief

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

ID	artist_ID	RecordID	Record Title
1	76	76
2	220	76
3	1202	1211
4	1212	1211
5	193	196
6	196	196

 

[mike60smart]

Reason you are having problems is because RecordID in the Junction table should be joined on RecordID Autonumber in tblRecords

 

[lodmark]

Lief

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

ID	artist_ID	Record_artist_ID	Song Title
1	76	76	You're The One That I Want
2	220	76	You're The One That I Want
3	1202	1211	When I'm Sixty-four
4	1212	1211	When I'm Sixty-four
5	193	196	No Moore Tears Enough Is Enough
6	196	196	No 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.

Leif

 

[Dreamweaver]

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

 

[lodmark]

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]

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]

Hi Leif

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

 

[lodmark]

Something like that!
This is the form that I'm using for the artist.

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:

Or this for the record:

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