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]

Take it in steps.

Start with a simple SELECT query and work on setting your criteria to make sure it is selecting the correct records for updating (do not worry about fields you are updating yet, just get the record selection (criteria) used in determining which records need updating.

Once you have that piece working, then you can work on which fields you want to update. From there, you might have to use IIF statements. For example, if a condition is met, update the value to something else, otherwise, just return itself.

Another option would be to have ten different Update queries, one for each field. You could then create a macro you can run all 10 with a single click instead of running each one manually.

 

[hatstand]

I'm getting closer!

I followed you directions and have moved a little closer the solving my problem. I made a Select Query and worte the following formula's:

FieldA: IIf([Table2].[Colour] Is Not Null,[Table2].[Colour],[Table1].[Colour])
FieldB: IIf([Table2].[Amount]>0,[Table2].[Amount],[Table1].[Amount])

As the fiedls are Text and Number. I changed the formula to suit. These both work perfectly but when I convert the Query to an Update Query, enter the Update to: [Table2].[Colour] & [Table2].[Amount]. It says that the formulas are not a valid name and that I should check if teh punctuation and characters are correct.

I'm getting closer I think.

 

[Joe4]

Sorry, I was away for a few days.

The IIF part of the clause will be your criteria, and then the THEN part will be in the UPDATE TO line (just the one that changes). I would recommend doing just one field update at a time, at least initially until you got it working.

If you are still having trouble post back.

 

[hatstand]

Thanks for your reply.

I now have it working. I entered:

IIf(IsNull([Table2].[Field]),[Table1].[Field],[Table2].[Field])

Into the Update To part of the query, obviously changing the field name to suit. Thanks for your pointers, patience and time. It's much appreciated.