Struggling to update two tables at the same time. (3 Viewers)

[Aurelius]

I think I've messed up but I don't know how.

A few months back I needed a music practice log to track my guitar\piano\harmonica practice. It needed to be functional straight away so I threw together some basic functionality. I made a "licks" table which contains a screenshot of a lick\riff I want to work on with target tempo and other good-to-know info about it. Then I have a logLicks table which records the dateTime, tempo I practiced it at, comfort level, mastery level. Basically stuff to measure how well I'm progressing in this area. Fairly straightforward stuff, and it was easy to make forms to work with this relationship.




I then did the same with other stuff I wanted to practice, like arpeggios, scales etc. Each of those needed a different "logX" table because I would record different information. With scales I wanted to record which key\position I was playing in. This is pretty important for some instruments like guitar, less so for others. However I needed to make separate forms for all this too.

Then I thought, wait I'm always recording Datetime, Tempo, Comfort, Mastery, so why don't I create a separate "Log" table, and keep the exercise specific stuff separate. I thought I was being very DataNormalization-y, and was very proud of myself until I realized I couldn't get it to work.


logScales records the scales I practiced, with a reference to the logID which records the date\time,comfort,mastery

I cannot make a form that updates both logScales and logID at the same time. I downloaded a couple of Many-To-Many examples and I still couldn't figure it out. I could make a form that updated "log" but it wouldn't put the corresponding logID into "logScales", so I end up doing that manually until I figure it out.

I'm not completely tech clueless, but I don't know what I don't know, so if someone could point me in the right direction I'd really appreciate it.

 

[June7]

So each log will have many scale and each scale can associate with multiple log? Many-to-many relationship requires a form/subform arrangement.
Main form bound to Log and subform bound to logScales with a combobox to select scale.

 

[Aurelius]

So each log will have many scale and each scale can associate with multiple log? Many-to-many relationship requires a form/subform arrangement.
Main form bound to Log and subform bound to logScales with a combobox to select scale.

You know this works, so that's progress! It's writing to both tables, so thanks! However, I guess I was coming at the problem from the wrong direction. I was thinking from the other way where LogScales is the main form and Log is the subform.

What I was thinking was I select the scale I want to practice on, and my subform loads the relevent Log information. It would work but only in a readable form. Your way does it differently but at least writes the data to both tables. I probably needed some complex queries to get it to work the way that's in my head.

 

[June7]

I should have said 'normally requires'.
LogScales is the 'junction' table. Could have it as standalone form then would need two comboboxes, one for Log and one for Scale. However, I don't think this will work as well.

Not sure what you were envisioning but I doubt any 'complex queries' would have accomplished, at least not SELECT queries. Perhaps VBA and action queries would.

 

[Pat Hartman]

This isn't what normalization is all about. The dates and time belong with the table that records the event. There is no benefit to separating them and there are downsides with working with them this way.

 

[Aurelius]

This isn't what normalization is all about. The dates and time belong with the table that records the event. There is no benefit to separating them and there are downsides with working with them this way.

I'm now realizing this. However, I thought I was doing this the wrong way because to get something simple like total time practiced required an SQL statement that had multiple UNIONs to add up all my different table types. I figured that if something that simple couldn't be done in Design Mode I was doing it wrong.

 

[Pat Hartman]

OK. If you're lucky and have only test data just delete it and but the fields back where they belong. If you have data that needs keeping, it will be a little harder. You'll need to add the fields to the correct tables. Then you will need to run a separate update query for each table. Join on the FK which I think you stored in the "dates" table and update the fields you moved back.

 

[gemma-the-husky]

You shouldn't have to write to more than one table. Why do you need to write to 2 tables?

 

[murphybridget]

That sounds like a smart setup for tracking your practice progress! Having a dedicated log for each instrument and including details like tempo, comfort level, and mastery level can really help you stay organized and focused on your goals.

I think I've messed up but I don't know how.

A few months back I needed a music practice log to track my guitar\piano\harmonica practice. It needed to be functional straight away so I threw together some basic functionality. I made a "licks" table which contains a screenshot of a lick\riff I want to work on with target tempo and other good-to-know info about it. Then I have a logLicks table which records the dateTime, tempo I practiced it at, comfort level, mastery level. Basically stuff to measure how well I'm progressing in this area. Fairly straightforward stuff, and it was easy to make forms to work with this relationship.


View attachment 104313

I then did the same with other stuff I wanted to practice, like arpeggios, scales etc. Each of those needed a different "logX" table because I would record different information. With scales I wanted to record which key\position I was playing in. This is pretty important for some instruments like guitar, less so for others. However I needed to make separate forms for all this too.

Then I thought, wait I'm always recording Datetime, Tempo, Comfort, Mastery, so why don't I create a separate "Log" table, and keep the exercise specific stuff separate. I thought I was being very DataNormalization-y, and was very proud of myself until I realized I couldn't get it to work.

View attachment 104314
logScales records the scales I practiced, with a reference to the logID which records the date\time,comfort,mastery

I cannot make a form that updates both logScales and logID at the same time. I downloaded a couple of Many-To-Many examples and I still couldn't figure it out. I could make a form that updated "log" but it wouldn't put the corresponding logID into "logScales", so I end up doing that manually until I figure it out.

I'm not completely tech clueless, but I don't know what I don't know, so if someone could point me in the right direction I'd really appreciate it.

 

[The_Doc_Man]

For an at-home practice log, exacting structure does not matter quite so much and therefore, @Aurelius, take the advice here to make what you have a working and functional record but it is OK to have minor variations from perfection. Functionality is the key.

However, @murphybridget, please note that a separate log for each instrument when the contents of those logs are otherwise nearly identical makes that structure denormalized from a theoretical point of view. The normal way to do this is to add one more field to the log table to note the instrument being practiced and then use queries when you want to focus on specific instruments or specific styles.

Personally, my favorite instrument for vigorous but brief practicing is the Pandemonium, invented by P.D.Q. Bach. It is the loudest known instrument when not considering electronic amplification of the sound, so people know when you are practicing - and they are relieved when you are done. Since Prof. Peter Schickele has passed away, we have lost the most prominent link to this interesting but somewhat messy instrument. In memory of the good professor, read about him here.

(Forgive the diversion into humor, but at least I kept part of it more technical.)