update query deletes updates?

[misscrf]

Here is the situation. I have 2 people helping me work on updates of this list. It is a list of our class for our class reunion. We all started out with an excel spreadsheet that has everyone's names ( first name, last name) and all the other fields were blank.

Now person 1 sends me their updates, and I am pulling them into Access, because that will be a better way to manage the data.

This is fine. update, no problem.

Here's the problem. The second person sends updates, but if I were to update the table again, their data didnt have the person 1 updates, only theirs. If I ran the update it would delete the updates of the previous person. See what I mean? It would update person "sam Jones " address to blank instead of the address that person 1 had submitted.

How can I create a process to update the main table with just the additions to the fields, without overwriting the additions that are there?

This shouldnt be too hard should it?

Please?

lol

Any help will be much appreciated.

ray:

 

[misscrf]

I tried the is null and did not get the desired results. Does this mean that I have to run a query for the update of each field for each user?

How should I set up the query, can you show me an example?
Please?
ray:

 

[Norie]

I tried the is null and did not get the desired results.

What was wrong?

Can you post the SQL for the query? You can see that by going to View>SQL with the query opened in design view.

 

[misscrf]

I'll have to do that when I get home tonight. I dont have the db here.
I will post it when I get home.

 

[misscrf]

here is the sql:

UPDATE [person1 Updates] INNER JOIN [new class list] ON ([person1 Updates].[First Name] = [new class list].[First Name]) AND ([person1 Updates].[Last Name] = [new class list].[Last Name]) SET [person1 Updates].[Last Name] = [new class list]![Last Name], [person1 Updates].[Name Change] = [new class list]![Name Change], [person1 Updates].[First Name] = [new class list]![First Name], [person1 Updates].Spouse = [new class list]![Spouse], [person1 Updates].Address = [new class list]![Address], [person1 Updates].City = [new class list]![City], [person1 Updates].State = [new class list]![State], [person1 Updates].Zip = [new class list]![Zip], [person1 Updates].Email = [person1 Updates]!, [person1 Updates].[Home Phone] = [new class list]![Home Phone], [person1 Updates].[2nd Phone] = [new class list]![2nd Phone], [person1 Updates].[Contact Preference] = [new class list]![Contact Preference], [person1 Updates].[Other/Comments] = [new class list]![Other/Comments]
WHERE ((([person1 Updates].[Name Change]) Is Null) AND (([person1 Updates].Spouse) Is Null) AND (([person1 Updates].Address) Is Null) AND (([person1 Updates].City) Is Null) AND (([person1 Updates].State) Is Null) AND (([person1 Updates].Zip) Is Null) AND (([person1 Updates].Email) Is Null) AND (([person1 Updates].[Home Phone]) Is Null) AND (([person1 Updates].[2nd Phone]) Is Null) AND (([person1 Updates].[Contact Preference]) Is Null) AND (([person1 Updates].[Other/Comments]) Is Null));


Let me know what you think.

i am thinking I am going to have to give up on automating this and just do it manually....

 

[Andrew Fergus]

Have you linked every field between the "person1 Updates" table and the "new class list" table? If so, there's your first problem. If a change has been made in the "person1 Update" table (in say the address field) then it won't match the record you have stored in the "new class list" table because the address is different.

You only need to link the fields containing the first and last names to get a match. You need to delete all of the lines connecting the two tables in your query, except for the lines between the first names in the two tables and the last names in the two tables.

The second problem is all of the "Is Null" criteria - you stated previously that one helper might update one field (e.g. e-mail) but another helper might update another field (e.g. address) for that same person.

So the short answer to this question .....

Does this mean that I have to run a query for the update of each field for each user?

..... is "Yes".

If you are going to do this with queries (instead of code) then you will need one query for each field that you are wanting to update. If the first helper updates the e-mail address then this is no longer "null" for the 2nd helpers file and the updated record will be excluded when you try to use the file from the second helper. So you need to have one of these queries for the e-mail address, one for the name change, one for the address etc etc etc.

I realise this is not the answer you're looking for but unless someone here can write some code to do this, then you don't really have too many other options.

Andrew.

 

[misscrf]

I do only have first name and last name connected in the query. If I have to run a separate query for each field, this wont work.

The one person updating a couple of emails ,and another some addresses was an example.

One person may update contact info for 5 people, and another person for 7 different people. ALL CONTACT INFO.

I guess I give up...