Filling Junction Table With ID's from 2 other Tables

[ringy9]

Windows 10, Access Office 365

Hi everyone.

Just joined forum in the hope I can learn as much as possible form the members of this group.



I have created 3 tables in Access as a Music database. The Tables I have created are:

tArtist: PK ArtistID , Artist

tArtist_Song: PK ArtistID , PK SongID

tSongs: PK SongID , Song_Title

Both the tArtist and tSongs are filled with records and the ID's are autonumber. I would like to fill the tArtist_Song Table with the ID's from the other 2 Tables.

I have attempted to Join this information, but no amount of manipulating the data, or searches I have Googled have helped me with this.

In the Design View I have attached, the Query will give the ArtistID and SongID as headings but of course doesn't fill the table with records.

I would be most thankful for any assistance.

[davegri]

MTM MedleyMultiselect-davegri-v03.zip

No quick way to do that with a query. No way for the query to know how to relate an artist to a song or a song to an artist.

Have a look at this to see how a form (with keyboard input from you) can help. The example is relating club members to events in one form and events to club members in the other.

The side by side listboxes are probably the fastest but should be the other way around, with the combobox selecting the artist and then the listboxes assigning the songs. That form is pretty heavy with VBA.

Simpler is the form/subform setup. The member is the main form with the events for that member in the subform. Very little VBA, and has the desired parent/child setup.

[ringy9]

Thank you davegri for your kind reply. I will investigate your file with interest.

[June7]

In order to appropriately associate artists with songs requires good old tedious boring data entry. I am sure Dave's examples cover the variations of data entry forms.

Unless you want to develop code that could pull this from mp3 file metadata. https://social.msdn.microsoft.com/fo...es-mp3-wma-etc

[ringy9]

So davegri from what I gather from your forms is that the input for your Junction table was input manually via the form is that correct? I'm thinking I'd be happy with the main/subform type setup with the Artist in one and The Song Titles in the subform. Does this input update the various tables? Can you also add new records via the form as well. Sorry for all the questions but there is a lot going on with the forms and I don't have to much knowledge of VBA if that is required.

[mike60smart]

Hi
You may find the attached example a little easier to follow.

Study the Relationships and then break down the Forms.

[ringy9]

Thank You mike60smart I will certainly check it out as well as your link

[davegri]

So davegri from what I gather from your forms is that the input for your Junction table was input manually via the form is that correct? I'm thinking I'd be happy with the main/subform type setup with the Artist in one and The Song Titles in the subform. Does this input update the various tables? Can you also add new records via the form as well. Sorry for all the questions but there is a lot going on with the forms and I don't have to much knowledge of VBA if that is required.

Answer to all is YES.
Have you tried to use the form?
Add a new member
Add events to a member record
Delete a member
Add a new event

All of these update the tables.

If you upload your DB, I can adapt the form to your data.

Mike60smart shows that the arrangement can be assigning members (artists) to events (songs) rather than assigning events (songs) to members (artists). In either case the result in the 3 tables is exactly the same.

[ringy9]

Database1.zip

Hi Guys, as suggested I have attached the zip file for my database.(Hope it has actually attached )

I'm guessing I'm in a different timezone (Melbourne,Australia) so I didn't get a lot of time to investigate the files sent to me, but I am doing so.

Thanks again for your generous help and support.

[Micron]

I'm guessing I'm in a different timezone (Melbourne,Australia

I visited "Malbin" on the best vacation of my life. Not helpful wrt a solution for your post, but I just wanted to say if I couldn't live in the great Cannuk land, AU would be a great alternate choice!

[davegri]

Database1.zip

Hi Guys, as suggested I have attached the zip file for my database.(Hope it has actually attached )

I'm guessing I'm in a different timezone (Melbourne,Australia) so I didn't get a lot of time to investigate the files sent to me, but I am doing so.

Thanks again for your generous help and support.

ringy9_artists_songs-davegri-v01.zip

Cleaned up the tables. Removed about 1500 duplicate artist names.
All the song titles had a leading space and some had leading open parens, which I removed. (also a couple of hundred dupes here)

The attached DB uses the main/subform approach. I added several forms, a couple of reports and some VBA.
I think it will give you a solid starting position by giving you a method to maintain your tables.



Edit:
Also added referential integrity to tables and cascade deletes so that you don't get hassles in the event of an Artist or Song deletion.

[ringy9]

O Wow! Dave Thank you kindly for your efforts. Time for me to get to work