How to use data in one table to update a field in another table.

[mdnikki]

I have two tables. Table 1 contains bio info, Table 2 contains giving info [2010 Offertory]



I know I need to use an update query.

I just can't seem to get it to work. I joined the two tables since not all people in Table 1 have Table 2 giving info.

I added a field in Bio one for 2010 Offertory
So how do I get those that have giving history in Table 2 to populate that field in Table 1

Any help is appreciated.

I have attached a copy of my attempt.

[JoeM]

I do not have the ability to download your database from my current location, but think I may be able to address your question.

Why do you need to store the 2010 offertory in a field in Table 1? Generally, it is not a good idea to physically store any value in a table that can be calculated in a query. One of the reasons is because if the data in Table 2 were to change, your database would lose integrity if you did not subsequently run the Update query again in order to also update Table 1.

Most of the time, it is totally unnecessary to store calculated fields in tables when they can be derived at any time using a query. You can use queries for pretty much anything that you tables for (i.e. basis of Forms, Reports, exports), so there is usually no reason to write the information back to a table (unless you were going to delete the table where the values were calculated from).

[mdnikki]

Thank you for your response but the data won't change, it is past giving actually 2007-2009. I want this giving information in this table 2 to be in Table 1 along with the biographical info. Since I have multiple tables for one for each 2007, 2008 and 2009. I want to use each of these tables to update a field in Table 1. So the end result would be Table 1 containing all the bio information and 2007, 2008 and 2009 giving history.

[JoeM]

But what I am telling you is that is not necessary.
You can create a query doing a Left Join from Table1 to all your other tables, and get the results you want (without ever needing to run any Update queries).
What do you think it needs to actually be stored in the table, instead of calculated in a query?

[mdnikki]

I guess it doesn't. I can create a table from the joined query if need be. Here is my issue. I have 3 tables 2007, 2008 and 2009. I want to join the bio to each of these..

If I do a left join from the first table to the second, left join from the first to the third and left join from the first to the fourth, I end up with more records than I have in the original first table.

[JoeM]

I end up with more records than I have in the original first table.

Then you have a one-to-many relationship going, something that you will have to address whether you use the method I am describing or whether to do an update query like you originally wanted to.

Most likely causes are:
- Your Offertory table has multiple records per client. If that is the case, you will need to first create an Aggregate Query to totals the records for each client. Then use this Aggregate Query in the join with your Bio table.
- You are not including enough fields in your joins

[mdnikki]

Ok got it. I summed it first and then did the join...worked perfect. Thanks for your help.

[JoeM]

Your welcome!