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:

 

[Norie]

Well to filter on blanks use Is Null.

In your update query in the criteria for the address field put Is Null. This will mean that only records with no address will be updated.

This method can be used for other relevant fields.

 

[misscrf]

Thanks!

I will try that. It sounds like a great idea!

 

[misscrf]

It didn't work! lol. I have like 10 columns, and if I set their criteria to is null, I get nothing.

Wait, I did is not null,. lol I will have to try that again.

Here is a thought though. When I did that I put the criteria all on one line, and got nothing. I was just previewingt the query to see results.

I had also tried an IIF statement to fill if well, is not null, and otherwise keep that field. Even though I used the wrong null/is not null, is there another problem there?

I mean becuase I am doing this to more than one column, is there an issue with it being and or or?

Thanks! I wish I could just say add additions no delete. lol

 

[Norie]

You would use the null against any field that needs updating.

If you are only updating 1 field you don't need the rest of the fields in the query.

Can you actually post a sample of both sets of data and explain what you want to update and what you don't want to update?

 

[misscrf]

here is a sample:

first name / last name/ address / city/ state/email/phone/contact pref

(etc)

Now 3 people have the list that has nothing but first name and last name.

Here is the problem. Person 1 sends me their updates, which has some fields updated maybe 5 emails for different people.

Later, person 2 sends me updates. Problem is they have updates like several addresses, but they dont have those emails that person1 had updated, in the data they sent me.

Now if I try to run an update with the person 2 data, the emails from person 1's updates will be updated... to the empty field of person 2's data. It deletes that previous update in a sense.

Does this explain my issue better?

 

[Andrew Fergus]

Hi, the example provided by Norie should work - I have tested this and got it to work as follows :

I have assumed the data from the first persons is stored in a table called "contacts" and that the data from the second person is stored in a table called "contacts2" and that the two tables have the identical structure.

Create a new query, add the tables "contacts" and "contacts1", Click on Query -> Update Query, link the two tables on the first name and last name, add all of the fields from the "contacts" table to the query (except for the first and last names), in the criteria section enter "Is Null" under every variable in the first row, in the "Update section" of the address field enter (using your table name and variable name) [contacts1].[address], in the "Update section" of the city field enter [contacts1].[city] and so on and so forth for every field.

Save aand Run the query.

If you preview the query and there are no records visible (i.e. you are testing the is null function), then it is probable that the first persons data has populated each field with a space (or something similar) which is not null and will prevent this and Norie's suggestions from working.

You can test it by looking at the data - try editing one of the supposedly null fields and see if there is already a space in there. If this is the case (i.e. the "empty" fields have a space) then let us know if you need a hand cleaning up the data before doing any updates.

HTH, Andrew.

 

[Andrew Fergus]

I have assumed the data from the first persons is stored in a table called "contacts" and that the data from the second person is stored in a table called "contacts2" and that the two tables have the identical structure.

Oops - the table names should read "contacts" and "contacts1" - I used "contacts1" in my example query.

BTW, if one person is updating the address and another is updating the e-mail address, then you should only include the field that needs to be updated on the query, rather than all of the fields.

HTH, Andrew.

 

[misscrf]

grrrr, I just lost this entire post somehow, let me type it again.


my previous example did not seem to explain my problem well, so I will try again.

3 people start with the beginning list. They send periodic updates of whatever they have been able to find up to that time.

Person1 sends updates of 2 different addresses, 5 different emails.

Person2 sends updates of 3 different addresses, 4 different emails, and 2 different phone numbers.

Person3(me) just updates manually to the master list. This is easiest lol.

Now if I update person1's updates to the master list, fine.

But if I then update person2's updates to the master list, I lose person1's updates. Even if I just did the email address or something. I would lose all the emails that were already in the master list.

Does that explain better?

 

[Norie]

How are you actually doing the updates?

Are you updating every single field of every single record, every single time.

If so that is the wrong way to do it.

What you want to do is update fields that have no information currently in them. This is where the Null part comes in.

By using it in the criteria for a query you can return those records where say the Email address has not been entered.