Multiple data entry for Junction Table

[Irish lad]

All,

Again, many thanks for this website. It is really helpful.
I managed to make my previous sticking point work (after some trial and error) but now I am REALLY stuck! :banghead:
The database has been working as I intended but I keep wanting to add new features. This is a music collection database. I am using it to track items such as Albums, Songs, Composers, Tracks on Albums, Artists on Tracks, Artists on Albums and so on. All that works well.

However, I would like to add Performers on Tracks (i.e. who played Guitar etc). I can figure out how to do this add at overall Album level but at Track level it requires some more work to avoid excessive data entry.

Essentially we have the following as part of the structure:

tblTracks
TrackID (PK)
AlbumID (FK)
TrackName
...

tblPerformer
PerformerID (PK)
PerformerName

tblInstrument
InstrumentID (PK)
InstrumentName

jtblPlayedOn
TrackID (FK)
PerformerID (FK)
InstrumentID (FK)

Where I am stuck is in deciding what is the best way to set up data entry for the Junction Table. Ideally I would like a form that would allow the user to select an album and show the tracks for that album. They could be selected individually or multiple (e.g. all tracks on an album if desired) and allow the Performer ID and Instrument ID to be entered.

I have been looking to Append Queries and using some sort of multi-select Listbox but it is all confusing to this novice.

Any advice greatly appreciated!

 

[Ranman256]

use 2 , SINGLE select listboxes.
user selects artist and an instrument, then click ADD button,
it runs an append query to add them both to the tPlayedOn tbl.
(I guess you could use dbl-click too)
similar to what i use:



Single choice boxes do not require code. Multi-pick do.

 

[Irish lad]

Thanks for the reply. What I can not see from that, though, is how to do that for multiple Tracks (and TrackID) at the same time as opposed to a single Track entry?

 

[MajP]

Did you ever get a solution to this? I played with this a little to see if I could come up with a intuitive solution. I had a class module that I was able to reuse. It simulates a "to from lists" control like you have in a wizard where you can move 1 or more selections from list to list.
My solution was a form for viewing and then a pop up form for adding. In the view form you have a series of linked continuous subforms. You select an album from a combobox and see all the tracks in subform 1. The second subform shows all the performers and instruments for the current track. If you select a record in the second subform it shows all the tracks they play on in the third. That is really not part of the solution, but trying to see how a user would manage the data and then make inputs.
If you want to enter performers you pop open the entry form. This allows you to select and album, performer, and instrument. It then has two listboxes "performed on" and "not performed on". You can then assign them to multiple tracks by clicking on a track or many tracks and moving them back and forth. Probably could do something similar with a single multiselect, but it seemed more intuitive to see the tracks performend on an the not assigned tracks. If interested I can post the solution, unless you figured out something better.

 

[Mark_]

The very complicated answer is "What genre of music is the album?" FIRST. Depending on type of music this can or won't make sense.

If you have a brass quintet doing classical pieces, often the same performer will always play the same instrument. For this type of music this works well. For others, not so much. If you start looking at track by track will find a lot of jazz where performers change up what they play. You also run into the issue of "Performer A does keyboard AND vocals on piece A, but only plays keyboard on piece B. On piece C they play something different entirely".

I'd check through your data source (album tracks) first and get a good feel for if your approach will actually work better for your preferred music samplings. I'd also make sure you include "Person A plays more than one instrument in a given track".

 

[MajP]

The OPs design accounts for all of that. The junction table has TrackID, PerformerID, and InstrumentID. Therefore you can have a single person playing different instruments on different tracks, multiple instruments on the same track, and multiple people playing multiple instruments on the same track. So when you do an assignment to a track/s it is both the performer and the instrument.

 

[Mark_]

MajP,

Not answering based off of tables, but off of his original desire to have an intuitive interface. If how the users will be using it doesn't match his user interface, how the data is stored isn't relevant to the users experience.

Not a question of "Can the tables support it", but "Does the original source for his data match how he's trying to input it".

 

[Irish lad]

Belated thanks to the responses. In answer to the question about interface, I will be the only user as am building a database for my personal collection. The tables as described will work for my requirements (a musician can play any instrument on any track and multiple versions thereof). I have also allowed a separate arrangement whereby we have album information and track information (if someone plays trumpet on every track then they get added to the album information and not per track - this was mainly to restrict data). There are different ways to do it of course but this works for me (thus far!)

As for the original question, I managed to sort it so I have three unbound boxes (Musician, Role and Track) on a form. After selecting what I want (including multiple tracks if required) it passes the underlying foreign key IDs on via a button to the appropriate junction table. This seems to work and I have used queries to pull all the data together for analysis.

That said, I would be interested in MajP's solution, not least as helpful to learn new things and see other approaches. Thanks.

 

[MajP]

When I deal with many to many relationships, I usually have functionality to change my view. In this case you want to have the ability to work at the Track level adding performers to tracks. Other times you will want to work at the Performer level assigning a performer to multiple tracks. This is also what I call an "assignment" database where you are not creating new data as much as assigning/relating records.

The first view is two synched continous subforms. The first subform is tracks on an album. The second subform are the performers on a track. If you have not done synched subforms before, take a look. It is pretty basic. I vomit every time I see a nested subform, I find them usually ugly and non-intuitive.

This is nothing complicated here. The first view is basically a native access subform, but the report view and the synch subforms make it very intuitive and easy to use. The second view is traditional multi select listbox code, but provides an intuitive view of what is and what is not assigned. You can click to add or remove or select all.

Track View

Performer View

RIP.

 

[Irish lad]

Thanks very much. This is really helpful and informative. I will be playing around with my database to improve it.

Another question please when I think about data entry. Is there a way to save time and force a second linked subform to update when the first subform is updated (where the underlying junction tables share a common key)? I have been trying in vain as follows:

1. Main form has Album details (including an Artist)

2. First subform shows Song for each Track (uses a junction table for tblSongs to tblTracks); it has a Master/Child on AlbumID

3. Second subform shows Artist for each Track (uses a junction table for tblArtists to tbl Tracks); it has a Master/Child on TrackID with the first subform.

This all works fine. However, for data entry I would like the second subform to update and write records with minimal effort after the first subform is updated. The Artist for the second subform is normally the same on all tracks (and the same as the main form). The second subform only has TrackID and ArtistID. I have tried the following:

a) Using an After Update to pop over to the second subform that already includes a default Artist. However, the TrackID is blank on the second subform unless I change something on it and then it updates with the first subform's TrackID. Correct results but requires manual work.

b) Using an After Update to pop up a different form that allows me to add Artist. It can use OpenArgs to transfer the TrackID (which works) but needs manual entry of the Artist (unlike option a which takes it from the main Form).

Any thoughts? In essence, I would like to fill out the first subform with all the Track/Song details manually and have the second subform default to TrackID (from first Subform) and ArtistID (from main Form) without any extra work.

As noted, it works fine for data already entered but I am looking to save time on entry (have a lot of CDs still to do!)

 

[MajP]

Do not talk about forms and subforms, explain what you want done at the table level. Forms and subforms are only windows into the tables.

This all works fine. However, for data entry I would like the second subform to update and write records with minimal effort after the first subform is updated. The Artist for the second subform is normally the same on all tracks (and the same as the main form). The second subform only has TrackID and ArtistID. I have tried the following:

I really cannot follow what you are saying. Forget the forms and controls you have now. Explain in simple words the actions that you would like to work, or present an image or the database. I have another demo that may interest you. This is a perfect candidate for a tree view. Access does not have a native tree view, but I have written an extensive class module that allows me to put any data into a working treeview with lots of features.

 

[MajP]

For sure I do this as a Treeview

Then if you click on a performer you can add the performer to multiple tracks.

Then I can click any node level.

 

[MajP]

Except I still would add the additional level. So my tree view would be

Group/Artist
 Album
  Track
   Performer/instrument.

with the ability to click and add at any level.

 

[Mark_]

Is there a way to save time and force a second linked subform to update when the first subform is updated (where the underlying junction tables share a common key)? I have been trying in vain as follows:

1. Main form has Album details (including an Artist)

2. First subform shows Song for each Track (uses a junction table for tblSongs to tblTracks); it has a Master/Child on AlbumID

3. Second subform shows Artist for each Track (uses a junction table for tblArtists to tbl Tracks); it has a Master/Child on TrackID with the first subform.

Artists should be a child to Track. As such, the "Second subform" should be a subform on the first subform.

What you are asking is, "Can I add a 'default' first child record when adding a parent? Yes, once you save the Track (and have its unique ID) you can add a record into the Artist table with your default Artist ID and the Track ID from your Track form. This would be an add query. If you want, you could even use Inputbox to ask "Add default artist for this track?" so you can skip adding records if that track doesn't feature the default artist.

Will you also be adding in their default role for the track?

 

[Irish lad]

Thanks both for the replies. I have come up with a solution, prompted by the helpful suggestions. I can use the same solution as with the roles issues discussed above.

1. Enter all Tracks with their Songs (junction table on TrackID and SongID) and other information linked to an album by AlbumID.

2. Once all entered pop up a Form that allows me to enter the Artist for each Track where I can multi-select (including a Select All). TrackID is the common link in the junction table.

3. I have kept Roles separate from Artists as that is how I think about music. I do not want a Role for "Primary Performer" or some such. So Elvis will be an Artist for an Album/Track but will also have a Role for "Vocals", "Guitar", "Piano" etc either by album or track level depending on the circumstance. That said, there is a common table for Musicians from which both Artists and Roles can be drawn.

4. I pull all this together in a variety of ways (helped by comments above) and have even managed to combine Tracks on Medleys and show concatenated Composers and such. Tracks with two Artists show up once and not twice as well in the final results.

5. I think there is no right way to build a music database as it depends on what you are trying to achieve. I have been very frustrated in dealing with Access and its rules but pleased at what you can end up with. I use Roon/Meridian (Sooloos) for playing my collection but think my own Access database will be more powerful in finding information (e.g. Songs are not always called the correct title on different albums even when they are the same composition but I can adjust for that).

Thanks again. Now for lots and lots of data entry...