Query that updates a record

[mastro78]

Two tables table1, table2. Table1 is constantly updated and is created from Excel. Table2 is where the Excel data is stored (an Excel import/link in Access). When the database opens, code is run that imports data from various Excel files into table1. It also runs an append query to update table2 with that information. However, table1 has about 30 fields that aren't updated all at once, about 5 fields are updated each week until there is a complete record. The main field updated from the start is the ID field. So table1 and table2 will have the value 12345 in the ID field. What I need is when new information associated to that ID field has been added to table1 it udpates table2 based on that ID field and the other fields that have been filled in for that record in table1 that table2 doesn't have filled in (Null) are filled in in table2. Complicated, I know, please help...totally lost here.

 

[mastro78]

Better description:

Table1
ID Company Date1 Date2
1234 Test 1/1/07 1/2/07
Table2
ID Company Date1 Date2
1234 Test
I want a way to update those two date fields with the data from Table1. Now there are about 30 more fields, and there is no particular time frame for which they are updated. So this will have to check to see if the ID number is equal, if the fields in Table2 are Null then update Table2 with the fields that aren't null in Table1. Will be run daily to check if updates have been made and fill in the pieces as more data is filled in in Table1 based on the same ID#.

 

[hovercraft]

UPDATE Table1 INNER JOIN Table2 ON Table1.id = Table2.id SET Table2.date1 = [Table1]![date1], Table2.date2 = [Table1]![date2];


Hope this helps,
Hovercraft

 

[mastro78]

That does work, but what I'm looking for is if the field is null in Table2, to update it with the value in the same field from Table1. The beginning of that statement works, I'm just lost as to what the rest of the SQL would be. I got it to work by using WHERE ((IsNull(Table2.Date1))); but i can't seem to get the syntax right for more than one field.

 

[hovercraft]

UPDATE Table1 INNER JOIN Table2 ON Table1.id = Table2.id SET Table2.date1 = [Table1]![date1], Table2.date2 = [Table1]![date2]
WHERE (((Table2.date1) Is Null) AND ((Table2.date2) Is Null));


But does it matter if the fields are null. If you have 30 so fields that could be changed at any given time then why not just update everything.

 

[hovercraft]

You could also change "AND" to "OR" in the WHERE clause.

Where Clause? - North Pole.

 

[mastro78]

I only want to update the null fields, what would be the best route for that approach?

 

[mastro78]

Maybe I'm making this harder than it has to be...if I just update every field other than the ID every time what syntax would i use? Or if the other approach would make more sense...any help is so appreciated!

 

[WyldeCarde]

UPDATE Table1 INNER JOIN Table2 ON Table1.id = Table2.id SET Table2.date1 = [Table1]![date1], Table2.date2 = [Table1]![date2]
WHERE (((Table2.date1) Is Null) AND ((Table2.date2) Is Null));

Have you actually tried this from hover? It should do exactly what you want, althought I would change the where clause to "OR" as suggested.

On the other hand duplicate data in two tables is a waste of time/space, if Table 1 is always correct, why not always get you data from that table and stop importing the duplicate data in Table2?

 

[randy700]

If you must have 2 tables, just run an update query to update ALL fields. If the fields are the same, there will be no changes. Eliminates the need to check for NULL values.


Randy