Update Table 2 When Table 1 is Updated (1 Viewer)

chineloogbonna · Oct 21, 2019

Hello,
I am trying to create an "Update Query" that updates Table 2 when table 1 gets updated. This seems like it would be simple, however, I can't get it to work.

I've tried various combinations of the expression in each field but nothing is working.

Table 1 and Table 2 are connected by "CallID" field on each table. Table 1 will only have one record with the CallID while table 2 will have 1 or more records with the same CallID.

I have a form that runs Query1 on btn click that should update data to Table 1 and Table 2 but it only updates Table 1.

My Update Query is as follows:
Field - TableFieldName
Table - Table1
UpdateTo - [Forms]![FormName]![txtBoxName]
Criteria- [txt_CallID] (Placed only in ID Table Field Name)

Then I added the same thing above for Table 2.

Hope that makes sense.

Thanks!

June7 · Oct 21, 2019

Actually, makes no sense to me. Perhaps you need to build form/subform arrangement?

theDBguy · Oct 21, 2019

Hi. I'm lost too. What information are you trying to update in Table2 and what information was updated in Table1?

chineloogbonna · Oct 21, 2019

A query using JOIN and relationship defined tables?

theDBguy · Oct 21, 2019

chineloogbonna said:
A query using JOIN and relationship defined tables?

Hi. Was that to answer my question above? If so, I'm still lost. Sorry.

June7 · Oct 21, 2019

Still no sense. If you want to provide db for analysis, follow instructions at bottom of my post.

chineloogbonna · Oct 21, 2019

Table1 - ProjectName,Items, NeedByDate
Table2 - ProjectName,Items, NeedByDate, BusinessName, ContactName etc.

I created a form to allow me to update just the Table1 data, however, I would also like the corresponding fields on Table2 to also be updated based on the CallID feild that is shared between both tables.

theDBguy · Oct 21, 2019

chineloogbonna said:
Table1 - ProjectName,Items, NeedByDate
Table2 - ProjectName,Items, NeedByDate, BusinessName, ContactName etc.

I created a form to allow me to update just the Table1 data, however, I would also like the corresponding fields on Table2 to also be updated based on the CallID feild that is shared between both tables.

Okay, assuming you meant that if you update any or all of the following fields in Table1, you also want to make sure that the same fields in Table2 match: ProjectName, Items, and NeedByDate, correct? If so, then the sure fire solution is to remove those three fields from Table2. Actually, storing duplicate information in multiple tables is against normalization principles and puts your data integrity at risk. Why do you think you need to store redundant data like this? Just curious...

June7 · Oct 21, 2019

Again, possibly should build form/subform arrangement.

Should not duplicate ProjectName,Items,NeedByDate in both tables. Use autonumber field as primary key in table1 and save that as foreign key in table2.

Or maybe this should be 1 table instead of 2?

chineloogbonna · Oct 21, 2019

I don't think I need redundant data. I thinking a whole bunch of tables would slow it down. So I had multiple tables that house extra data for fields that get used with sometimes. I don't always think the way you guys do, but Im learning. I need to separate basically.

theDBguy · Oct 21, 2019

chineloogbonna said:
I don't think I need redundant data. I thinking a whole bunch of tables would slow it down. So I had multiple tables that house extra data for fields that get used with sometimes. I don't always think the way you guys do, but Im learning. I need to separate basically.

Hi. If you have problems with speed, maybe we can try to fix those instead. If I was given a choice between slowness or incorrect data, I'll probably tell users to grab a coffee and chill. Just my 2 cents...

chineloogbonna · Oct 21, 2019

Thanks! Couldn't figure out how to do relationships, forgot about the foreign key.

June7 · Oct 21, 2019

If you think bunch of tables will slow it down then why do you have multiple tables? Statements seem contradictory.

Not necessarily helpful to separate fields that 'get used with sometimes'. Yes, some records might have a bunch of empty fields but table will still work.

If you do insist on multiple tables then build form/subform and use autonumber as primary key. Avoid compound keys.

theDBguy · Oct 21, 2019

chineloogbonna said:
Thanks! Couldn't figure out how to do relationships, forgot about the foreign key.

Hi. You may have forgotten what it was called, but you're actually already using it. You said Table1 has one record with CallID in it and Table2 has one or more records with the matching CallID. Here's a quote from your first post:

Table 1 and Table 2 are connected by "CallID" field on each table. Table 1 will only have one record with the CallID while table 2 will have 1 or more records with the same CallID.

That's the very definition of a Foreign Key.

chineloogbonna · Oct 21, 2019

Yeah, I see. Takes me a min sometimes. Also, I just gave random examples above not realizing the significance. I only have two fields that are redundant, ProjectName and NeedByDate. I need to remove those from Table2 and and make sure my keys are in place.

Thank you!

theDBguy · Oct 21, 2019

chineloogbonna said:
Yeah, I see. Takes me a min sometimes. Also, I just gave random examples above not realizing the significance. I only have two fields that are redundant, ProjectName and NeedByDate. I need to remove those from Table2 and and make sure my keys are in place.

Thank you!

Hi. You're very welcome. June7 and I were happy to assist. Good luck with your project.

The_Doc_Man · Oct 21, 2019

Database normalization would allow you to remove the extra data in places where you don't need it. From your description, your two tables REALLY are

Table1 - CallID, ProjectName,Items, NeedByDate
Table2 - CallID, ProjectName,Items, NeedByDate, BusinessName, ContactName etc.

IF this is correct, and if Table2 has the multiple entries, and if the goal was to set every record in table2 to have the same project name, items, and need-by date as the single record in table1...

Then take those fields OUT of table2 entirely. In that situation, they are redundant. THEN use a JOIN query to link table1 and table2 on the CallID field which they share in common. In the query, you can see everything including the details in table2 and the information in table1 that is common to all records in table2 with the same CallID as that record in Table1. From your description, I can't tell what else might be redundant, but based on the description you gave us, I think I am telling you something correctly.

Pat Hartman · Oct 22, 2019

The two tables look "backwards" to me. How about?

Table1 - BusinessID, BusinessName, ContactName, etc.
Table2 - CallID, BusinessID, ProjectName, Item, NeedByDate

Business should be the 1-side and calls should be the many side. You might even need an intermediary table depending on what fields are associated with Project

Table1 - BusinessID, BusinessName, ContactName, etc.
Table2 - ProjectID, BusinessID, ProjectName
Table3 - CallID, ProjectID, Item, NeedByDate

Update Table 2 When Table 1 is Updated (1 Viewer)

chineloogbonna

Registered User.

June7

AWF VIP

theDBguy

I’m here to help

chineloogbonna

Registered User.

theDBguy

I’m here to help

June7

AWF VIP

chineloogbonna

Registered User.

theDBguy

I’m here to help

June7

AWF VIP

chineloogbonna

Registered User.

theDBguy

I’m here to help

chineloogbonna

Registered User.

June7

AWF VIP

theDBguy

I’m here to help

chineloogbonna

Registered User.

theDBguy

I’m here to help

The_Doc_Man

Immoderate Moderator

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread