Update lookup table from main table

[Infinite]

Hello again! I have a tblShows and a tblShowCosts. TblShowCosts has a lookup field that it gets its info from the tblShows. Now, I have a form frmEvents I can use to edit the show names. What I am trying to do is, when I edit the show name from frmEvents and it changes in tblShows, I want it to also change in tblShowCosts. My current problem with this not working is that if I were to change the show name, then all my sales for that show will not exist, as they no longer go the that show.

For instance, I have the show ShowName1 and it has 500 sales. If I were to change the show to Florida State Fair, then all 500 of those sales still go to the now non existent ShowName1. If I get this to work, then ShowName1 will change to Florida State Fair.

Thanks!

 

[Ranman256]

if you want the costs to auto-update, you would not add the show and cost,
you would add the table KEY for that show & cost.
Then when the cost changes, the query will show this.

or method2
if you did add show and cost, youd have to run an update query to update your tables values. And youd have to do it frequently. so this is not the best method.

 

[Infinite]

I found out what I had to do, and this create a Relationship between the Show in tblShowSales and the tblShows. But, the problem I have is that my shows have names, like MO State Fair 08-2014. That 08-2014 comes from the qryAdminEvents that adds the StartDate to the ShowName. But, the problem I have is, how do I create a query that has the Referential Integrity enforced? I dont know how to enforce inegrity between query's and tables. I was thinking maybe create a update query, but that wont work at all, as I have more sales then show records. So it wouldn't be able to update my records. I would need to create a table that is updated from the qryAdminEvents and then tblShowSales Show field would get its info from tblShowNames (the new table I would create) and then I would enforce referential integrity between those 2. But then I would have to keep updating that whenever I change a show name...But, I could just have a OnDirty command that auto runs that update...Hmm. That might work.



FYI: tblShowCosts are the expenses of the show. tblShowSales are the sales from the show.