Results 1 to 4 of 4
  1. #1
    jorgenfi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    2

    Automatic cascade update of table based on entries in query


    Hi,

    I have a database with several tables including these two:

    1. List of components, including a field with a code for signals
    2. 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!

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Tables don't care where the triggers originate for cascade deletes and updates. Should work fine with the queries.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about showing readers a jpg of your tables and relationships --make sure you expand tables so all fields are visible.

    You might also consider a brief overview of what your subject matter is in simple, plain English.
    Signals, components???

  4. #4
    jorgenfi is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    2
    Click image for larger version. 

Name:	Component.PNG 
Views:	7 
Size:	6.7 KB 
ID:	37106Click image for larger version. 

Name:	Signal.PNG 
Views:	7 
Size:	9.0 KB 
ID:	37107Click image for larger version. 

Name:	IO list.PNG 
Views:	7 
Size:	15.9 KB 
ID:	37108Click image for larger version. 

Name:	SignalData.PNG 
Views:	7 
Size:	14.9 KB 
ID:	37109

    Thanks for suggestion. Here are the images.
    Component = table 1
    Signal = table 2
    IO list = query
    SignalData = table 3

    IO list sql query:
    Code:
    SELECT (Component.ID & "K" & Signal.ID) AS [SignalKey], Signal.Tekst, Signal.SignalType, (Component.TAG & ";" & Signal.SignalTAG) AS FullTag FROM Signal, Component
    WHERE ((([Component].[TagType])=[Signal].[TagType]));
    So, basically the query generates a list made by data generalized in table 1 & 2, while table 3 is containing more info about the instances in the query, info that can not be generalized.

    What I want, is to automatically update the entries in table 3 so they matches the outcome-table of the query. So that I can fill in more info in the fields to the right in that table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Automatic update query
    By jdebiso in forum Queries
    Replies: 10
    Last Post: 04-18-2017, 07:40 AM
  2. Update table entries not quite right
    By DubCap01 in forum Modules
    Replies: 4
    Last Post: 03-14-2017, 06:05 AM
  3. Replies: 7
    Last Post: 12-09-2014, 12:24 PM
  4. Replies: 1
    Last Post: 01-21-2013, 12:15 PM
  5. Replies: 6
    Last Post: 05-10-2012, 08:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums