Update multiple tables with one Update Query?

[Ingeneeus]

I've searched the Queries forum until my eyes hurt, but haven't found a solution. I'm hoping someone here finds this post and can offer some help. I should point out that I've had exactly NO Access training, and am essentially self-taught (so a lot of what I read here is WAAAAY over my head). I will try to provide as much background as possible in an effort to clarify what I need. Apologies in advance if I am dispensing Too Much Information.

The company I work for is essentially an institutional purchasing service -- we supply movies on DVD. One of the features we offer is that we keep track of when movies that fit certain criteria (Academy Award Winners, BAFTA nominees, etc) are released, and supply them to our customers.

I have multiple tables which are divided by what I'll call Award Category; in these early stages I'm working with two: Academy Awards and Sundance. Each table contains the following Title (most important), Award Category (best picture, etc), Winner (Y/N).

I also have a table named Film Titles Joined, which lists ALL of the films which are included in the other two tables. This table lists Title, Street Date (when the DVD is going to be released) and general Notes.

After MUCH trial and error, I have managed to create a form called (imaginatively enough) Films with Street Dates, which allows us to view all the films that have a declared Street Date. This form uses the Film Titles Joined table as its Record Source. After much MORE trial and error, I created another form which, when opened, displays the Title, Street Date, and Notes of whatever record was selected in Films with Street Dates. This second form has subforms which list the award categories that each film qualifies for (this from the Academy and Sundance tables).

So far, so good. Works like a charm! The problem is that my boss would like to be able to make changes in the film's title from this form. This is reasonable, since titles often do change from the festival circuit to final release, particularly foreign films.

This is where I'm in over my head. I need to be able to change the title in the Film Titles Joined table and have it change the title in the Academy and Sundance tables as well. I created an Update Query to do this, and it works just as desired, but only for ONE table. By this, I mean that I can run the update query where the name gets changed in both the FTJ table and the Academy table, but when I tried to add the Sundance table to the mix, it wouldn't change that table.
I've got to be able to keep the titles consistent in all the tables. I've read that I can't do a multi-table Update Query, but I'm in a bit of a bind since it's not really practical to restructure the database with different tables.

Someone suggested that I run multiple update queries via a macro, which I tried (I just copied and pasted my first Update Query, renamed it, and switched the target table), but I must have missed something because it works the first time (Academy table) "You are about to update two rows" ==> "OK" but not the second time (Sundance table) "You are about to update zero rows."

I'm guessing that this might be because the second Update Query is trying to reference a film title in the Film Titles Joined table which has already been changed by the first query. The thing is, I don't know what to do about this.

Any suggestions for a work-around would be MOST appreciated. Any suggestions to scrap everything and start over with a better table structure will be less appreciated.

I'm really hoping somebody can offer some advice.
Thanks in advance,

~Gene

 

[Guus2005]

First a not so popular remark: you should normalise your database! Use Id's and not actual film names to link tables together!
This is very important! For future reference. Think about it! Did i mention it was important? Well it is.

Other not so fast things you can do to minimize the damage is to use cascading updates and cascading deletes. Go to the relationships screen and create the relationship

Each time you update or delete a key field, it is changed in the other tables.

Share & Enjoy!

P.S.

Here is some reading on the subject

 

[Ingeneeus]

Thank you so much for responding. I'll agree that I should have used IDs when I was setting up the tables -- I wasn't sure how to make that work when I was getting started. Since some films exist in Academy and some in Sundance, and some exist in both, I couldn't figure out how to build the relationships with the automatically-assigned IDs.

I greatly appreciate your assistance on a work-around. I think I've got something wrong somewhere, though (shocker, I know).
I attempted to establish the relationship with Referential Integrity and Cascade Update, and I got an error that states that "Microsoft Office Access can't create this relationship and enforce referential integrity" because data in the Sundance table violated the referential integrity rules.

Evidently I did something right when I set up the relationship between my Film Titles Joined table and the Academy table because that relationship is referential integrity enforceable and cascade updatable. It also has the little infinity symbol on the link line. I'm going to try to include a screen-capture.

 

[Ingeneeus]

Ah HA! I got it. I took another look at the popup I was getting: "For example, there may be records relating to an employee in the related table, but no record for the employee in the primary table. Edit the data so that the records in the primary table exist for all related records."

So I copied my two tables to an Excel sheet, ran a VLOOKUP to look for discrepancies, and voila, there was a misspelling in ONE title on the Sundance table. I fixed the record, created the cascade update releationship you outined above, and ran my update query.

Worked like a charm.

Thank you again for your help!

~Gene