Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Access 2000 - Update Query to update each field only if it is not null

GrayZone E11GrayZone E11 Member Posts: 2
I have a table containing 277 fields (I know, I know...did someone say 'relational database'... I didn't design it, I am just working on it for friends in dire need... :)

I currently have an import process which extracts data from a spreadsheet and then appends it to a staging table identical to the monolithic main table. The user then clicks to run a regular old Update Query which updates the main table with the staging.

The above was on-the-hoof emergency stuff to upload one-off updates to the data, but due to outside issues this has to be used as a regular update feature, urgently. (isn't it always...)

My problem is that these updates are likely to only contain the fields being changed. Therefore the staging table has null values in the fields not being updated. Of course, if I run this update query it will update the new fields, but also blank those which aren't being updated...

If you set a condition not to update a field if it is null, it obviously will not update any fields in that record.

Is there a quick way to get the query to update only those fields which have a value in the staging table? I can write some code to do this but basically by the time I have dusted off the cobwebs, written the code and tested it properly, the hour of need will be past.

Any quick-fixes would be very much appreciated.

Cheers

Gray

Comments

  • dokken2dokken2 Member Posts: 532
    : I have a table containing 277 fields (I know, I know...did someone say 'relational database'... I didn't design it, I am just working on it for friends in dire need... :)
    :
    : I currently have an import process which extracts data from a spreadsheet and then appends it to a staging table identical to the monolithic main table. The user then clicks to run a regular old Update Query which updates the main table with the staging.
    :
    : The above was on-the-hoof emergency stuff to upload one-off updates to the data, but due to outside issues this has to be used as a regular update feature, urgently. (isn't it always...)
    :
    : My problem is that these updates are likely to only contain the fields being changed. Therefore the staging table has null values in the fields not being updated. Of course, if I run this update query it will update the new fields, but also blank those which aren't being updated...
    :
    : If you set a condition not to update a field if it is null, it obviously will not update any fields in that record.
    :
    : Is there a quick way to get the query to update only those fields which have a value in the staging table? I can write some code to do this but basically by the time I have dusted off the cobwebs, written the code and tested it properly, the hour of need will be past.
    :
    : Any quick-fixes would be very much appreciated.
    :
    : Cheers
    :
    : Gray
    :


    don't know of a quick fix. you could have a query for each specific field, although that would result in 270+ queries.

    or you could use dao/ado to traverse thru the records and handle each field that way. either solution will not be quick.
  • GrayZone E11GrayZone E11 Member Posts: 2
    After a little time I did produce a quick-ish solution...

    In each value field I entered an Iif statement to look at the value of the update data, and if it was null then the value from the main data table is used.

    i.e.
    IIf([UpdateTable].[Field1]<>"",[UpdateTable].[Field1],[MainTable].[Field1])

    Hope this may help someone else in the future. I didn't mind doing code in the query, but didn't want to go into the whole code testing routine of VB/SQL.

    Thank you very much for your reply, it's good to know people chew these things over and respond.

    Cheers

    Graham
Sign In or Register to comment.