Struggling to update two tables at the same time. (1 Viewer)

[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?