Update Table 2 When Table 1 is Updated

[chineloogbonna]

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]

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

 

[theDBguy]

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

 

[chineloogbonna]

A query using JOIN and relationship defined tables?

 

[theDBguy]

A query using JOIN and relationship defined tables?

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

 

[June7]

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

 

[chineloogbonna]

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]

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]

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]

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]

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]

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

 

[June7]

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]

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]

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]

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]

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]

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