Update Query Help

[hatstand]

I’m new to Access and in the process of creating a database that is populated via Excel. I’ve created a linked Excel table and Access table. Then created an Append Query to do the updating. The Query updates the table in blocks of 50 records daily. This works perfectly.

I now need to create an Update Query. Creating the Query is straight forward enough. But on a daily basis I receive information that needs to be amended. My problem is updating one figure without wiping the whole record.

I know I need to write the formula in the field part. In Excel, the formula would be: =IF(A1<>"",1,2)

Any pointers would be much appreciated.

 

[Joe4]

An Update Query does not "wipe out" any records, it only updates certain field values of existing records.

Here are a few links that show you how to use an Update Query. See if that helps. Post back if you run into problems.

http://office.microsoft.com/en-us/access-help/update-data-by-using-a-query-HA010076527.aspx

http://www.databasedev.co.uk/update_query.html

 

[hatstand]

Hi, Thanks for the reply.

I can do the update, thanks to both pages. But the pages don't say if I only wanted to replace one value how to do it.

I was think along the lines of an update query with a Criteria in each field that says if Field1 Table1 is empty then leave the value in Field1 Table2. But if Field1 in Table1 has a value then overwrite Filed1 Table2.

I hope that makes sense.

 

[Joe4]

How are table1 and table2 linked?

If you have the query that joins these two tables, can you post it (change your query to SQL view, and cut and paste the code here)?

 

[Joe4]

See if this helps.

Assuming you have joined the two tables in the query, select to display only Field1 from Table1 and Table2.

Now, since you only want to select records where Field1 from Table1 is not blank, then enter criteria on the Criteria line under the Table1 Field1 field, something like:
Not Is Null
or
<>""

Now, change the query type to an Update Query, and enter the following on the Update To line under Table2 Field1:
[Table1]![Field1]

Now, if you run the Update Query, it should only update Table2 Field1 for matching records where Table1 Field1 is not empty.

 

[hatstand]

Hi,

I am using some test tables to get things working before attempting it on my main tables.
The SQL is:

UPDATE Table1 INNER JOIN Table2 ON (Table1.ID = Table2.ID) AND (Table1.Date = Table2.Date) SET Table1.[Date] = [Table2]![Date], Table1.Amount = [Table2]![Amount], Table1.Colour = [Table2]![Colour]
WHERE ((Not (Table1.Amount) Is Null) AND (Not (Table1.Colour) Is Null));

Table1 is ID = Autonumber, Date, Amount, Colour. Table2 is exactly the same layout. I have linked the ID and the date fields.

I tried Not is Null, which I was expecting to work. But it over wrote the number in Table1.

Am I missing the obvious

 

[Joe4]

I think you have it backwards. I thought you said you wanted to overwrite the values in Table2 Field1, but your code does just the opposite.

The snippet of code you posted below updates Table1, not Table2.

SET 
   Table1.[Date] = [Table2]![Date], 
   Table1.Amount = [Table2]![Amount], 
   Table1.Colour = [Table2]![Colour]

If you want to update Table2, you need to reverse them

SET 
   Table2.[Date] = [Table1]![Date], 
   Table2.Amount = [Table1]![Amount], 
   Table2.Colour = [Table1]![Colour]

 

[hatstand]

Hi,

Your right. I was rushing to get home and wasn't paying attention. The update works fine. But it still overwrites the whole record. I've tried Criteira: Not Is Null <>"" Is Null.

I think I'm missing the obvious but can't see what.

 

[Joe4]

What do you mind that it is updating the whole record? It should only be updating the fields in the SET clause of your SQL code.

Or do you mean it is updating your whole record set, meaning it is updating records (not fields), that it should not be? If so, try this criteria (NULLs and blanks are NOT treated the same in Access, this should catch both).

Not Is Null and <>""

 

[hatstand]

Hi,

If I had a table with 10 record and ten fileds. It would mean that I have 100 bits of information. What I can't work out is, if I wanted to replace 1 bit or many bits of information in one record using an Update query. How can I do without having to replce the whole record. I've tried every combination of Null and am now starting to think that using IIf is how to do it.

I've been trying to solve this for a few days now and am no further forward. Other than the help you have offered and doing lots and lots of web searching.

The search goes on.