Add new records across relationships
I m currently building a solution to help me. I am a tour manager for a band and am building a solution to keep track of information and to help speed me up. I have several tables, I have a table named "Show Advance", and one named "Merchandise."
I am having a couple of major problems so far.
I do all of my work in my "Show Advance" I create daily updates in this table Say on Feb 5th we play at Ven0001 and Feb6th we play at Ven0002 each of these its own record that stores information about that particular show for the day. I also have a table "Merchandise" which is another file, that stores all of the merchandise Sales for each Venue (Ven---). They are linked together by the Venue Id field.
The problem is this. I want all new dates that I add in the "Show Advance" table to be added to the "merchandise" table. Currently what I did was write a script that opens the "merchandise" file and then within the "merchandise file I have a script trigger set to run when opened that imports data in...and it is set to Match up the "Venue id" and to update data based on the venue id's being equal and to add new data as new records.
My problem is that it adds records that have already been in the table. It doesn't do it all the time either so I can't figure out why it adds records I already have only some times.
Is there a way that when I add a new record In "Show Advance" that it will automatically add a new record in "Merchandise" with that Venue Id, Name, etc.
Hopefully I was clear on that.