Hi,
I have a database with several tables including these two:
- List of components, including a field with a code for signals
- List of signals, based on code
E.g. the code PV1 have three rows in table 2, while another code SV1 will have only one row in table 2. This is done to generalize the signals for components, as many of them have same signals. The list of signals is then a query getting data from both tables.
Now, the problem is that I want to add more info on these symbols, info that are not the same for all components with the same code. I can add a new table for this, table 3, and have each record in this be connected to the signal list based on a field in table 3 with values identical to a value in my signal-query, which is basically a sting-concatenation of IDs from the two first tables.
However, here is my case: I would like the table 3 to automatically update so there is always a row in this table for each row in the signal-query. So for example, if I add a new component with a code, there signal query is automatically updated of course, but I would also like the table 3 to have new rows for the new signals. And vice versa, if I remove a component or change its code, I would like the table 3 to adjust its content accordingly.
I expect the only way to do this is by VBA-scripts triggered by updates in table 1 and 2, but I fear it will be very heavy to run. Also, it requires quite some coding. Is there any easier way to do this?
For example, the database relationship tools for cascade update related fields, and cascade delete related records are helpful, but they cannot be used for queries, only tables. I would like similar tools to work for relation from query to table, and to also cascade add records.
From a information point of view, there should be no problem with this, as there are clearly defined primary keys for all steps.
Any input on this problem is highly appreciated!