Update Query on Multiple Fields

[LisaC31]

Hello!

Just a heads up, I'm not skilled in SQL but I am familiar with creating queries in Design View of Access.

The problem I'm having is that I need to update an existing Access table (SAP Masterview) with corresponding fields from a linked Excel table (ZQM88 Qlik Tbl). I only want to update each field in SAP Masterview if it’s blank though.

I created the update query with Null as the criteria for each field but in looking at the SQL code, it looks like Access is taking this as an AND condition and all fields have to NULL. There will be instances where one field may be Null but others may not so therefore, no update required.

I tried to change the AND to OR in SQL but it did something weird in Design View which didn’t look accurate. Or maybe there's a more efficient method?

I attached screenshots.

Thank you in advance.

 

[isladogs]

Running update queries where a single field is null is easy but for multiple fields its problematic. Your existing query will only work where all fields are null (AND). TO convert to OR, you need to write the Is Null criteria for each field on a separate row. However that means all fields will be updated where any one of the fields is null ...which I doubt you want either.

The safest way would be to run a procedure to update each field in turn if null'.

However you may find some useful information in the following links
1. Synchronise Data - the second part may be particularly relevant to your situation.
2. https://www.access-programmers.co.uk/forums/showthread.php?t=303677&highlight=Update+null. This includes an innovative but complex approach developed by MajP which can work well but isn't yet totally 'user friendly/idiot proof'.

Hope that helps

 

[Pat Hartman]

If you put the is null in the criteria, that affects the rows that are selected. ALL rows selected will be updated and that is not what you want so changing the selection criteria to OR would result in EVERY column being updated if ANY column were blank.

You need to do this in the Set statement itself. This query updates every column of every row either with the existing value or with the value from the other table. You can make this more efficient by using a where clause with ORs so that only a record that actually needs updating will be selected. Prepare yourself for a whole lot of very annoying typing:

UPDATE [ZQM88 Qlik Tbl] as t Inner Join [SAP Master View] as s SET t.[Cust Originator ID] = IIf(IsNull(t.[Cust Originator ID], s.[Cust Originator ID], t.[Cust Originator ID]), t.Customer = IIf(IsNull(t.Customer, S.Customer, t.Customer), ....;


FYI - using embedded spaces and special characters is really poor practice. Although Jet/ACE allow you do create tables and columns with poor names, you cannot actually use them unless you are willing to enclose them in square brackets. This will make your SQL so hard to read it will almost certainly be filled with typos unless you are an excellent typist.

And finally, if this data is never updated in your app, then the best method is to simply replace the entire table. If you have custom fields that you want to keep in your app, keep them in a separate table so they don't interfere with this data replacement.

If you want a suggestion on how to replace entirely empty tables without causing bloat, post back and I'll tell you how to do it.

 

[LisaC31]

isladogs,

When you say create a procedure do you mean VBA or is this something that can be done in Access SQL?

 

[LisaC31]

Hi Pat Hartman,

Can you explain the "t." and "s." before the Cust Originator Id in your sample? Is it read as If SAP Masterview.Customer Originator Id is null, then ZQM88 Qlik Tbl.Customer Originator Id, Else SAP Masterview.Customer Originator Id?

I don't mind typing or doing a lot copying and pasting as long as I understand the format structure.

As far as the spaces, I hear you but it was created years ago by someone else and we're transitioning to a new system in a few months so don't want to go down the path of making major updates.

The table that is being updated is already a created table that has over 200k records and about 50 fields. I'm not sure I understand about replacing the entire table w/o causing bloat?

 

[arnelgp]

UPDATE [ZQM88 Qlik Tbl] INNER JOIN [SAP Master View] ON 
[ZQM88 Qlik Tbl].[Part ID  ] = [SAP Master View].[Part ID] 
SET 
[SAP Master View].[Cust Originator ID] = NZ([SAP Master View].[Cust Originator ID],[ZQM88 Qlik Tbl].[Cust Originator ID]), 
[SAP Master View].[Cust Originator] = NZ([SAP Master View].[Cust Originator],[ZQM88 Qlik Tbl].[Customer Originator]), 
[SAP Master View].[Batch Received Location] = NZ([SAP Master View].[Batch Received Location],[ZQM88 Qlik Tbl].[Batch Received Location]), [SAP Master View].[ZR Notification] = NZ([SAP Master View].[ZR Notification],[ZQM88 Qlik Tbl].[ZR Notification]), 
[SAP Master View].[Part# (HBAS)] = NZ([SAP Master View].[Part# (HBAS)],[ZQM88 Qlik Tbl].[Part # (HBAS)]), 
[SAP Master View].[Part Desc] = NZ([SAP Master View].[Part Desc],[ZQM88 Qlik Tbl].[Part Desc]), 
[SAP Master View].[Part#(Cust)] = NZ([SAP Master View].[Part#(Cust)],[ZQM88 Qlik Tbl].[Part # (Cust)]), 
[SAP Master View].[Part Serial #] = NZ([SAP Master View].[Part Serial #],[ZQM88 Qlik Tbl].[Part Serial #]), 
[SAP Master View].[Part Prod Date] = NZ([SAP Master View].[Part Prod Date],[ZQM88 Qlik Tbl].[Part Prod Date]), 
[SAP Master View].[Vehicle VIN] = NZ([SAP Master View].[Vehicle VIN],[ZQM88 Qlik Tbl].[Vehicle VIN]), 
[SAP Master View].[Cust Complaint] = NZ([SAP Master View].[Cust Complaint],[ZQM88 Qlik Tbl].[Cust Complaint]), 
[SAP Master View].[Cust Complaint Class] = NZ([SAP Master View].[Cust Complaint Class],[ZQM88 Qlik Tbl].[Cust Complaint Class]), 
[SAP Master View].[Cust Complaint Class 2] = NZ([SAP Master View].[Cust Complaint Class 2],[ZQM88 Qlik Tbl].[Cust Complaint Class]), 
[SAP Master View].[Part Type] = NZ([SAP Master View].[Part Type],[ZQM88 Qlik Tbl].[Analysis Detail]), 
[SAP Master View].[Conclusion Defect Reason] = NZ([SAP Master View].[Conclusion Defect Reason],[ZQM88 Qlik Tbl].[Conclusion Defect Reason]), 
[SAP Master View].[Analysis Subcomponent Part No] = NZ([SAP Master View].[Analysis Subcomponent Part No],[ZQM88 Qlik Tbl].[Analysis Subcomponent Part No]), 
[SAP Master View].[Analysis Start Date] = NZ([SAP Master View].[Analysis Start Date],[ZQM88 Qlik Tbl].[Analysis Start Date]), 
[SAP Master View].[Analysis By] = NZ([SAP Master View].[Analysis By],[ZQM88 Qlik Tbl].[Analysis By]), 
[SAP Master View].[ZP Notification] = NZ([SAP Master View].[ZP Notification],[ZQM88 Qlik Tbl].[ZP Notification]), 
[SAP Master View].[Main Activity Type] = NZ([SAP Master View].[Main Activity Type],[ZQM88 Qlik Tbl].[Main Activity Type]), 
[SAP Master View].[Q2 Notification] = NZ([SAP Master View].[Q2 Notification],[ZQM88 Qlik Tbl].[Q2 Notification]);

 

[isladogs]

isladogs,

When you say create a procedure do you mean VBA or is this something that can be done in Access SQL?

I meant vba code which runs each query or sql statement in turn.
Perhaps arnel's version of your update query will do the job without needing to do each field separately. Best to check on dummy data first

 

[LisaC31]

Thanks so much Arnel.

When I tried to run, I ended up getting an 'Enter Parameter Value' for ZQM88 Qlik Tbl.Field Name when trying to run the entire query.

I tried to isolate it and run it for one field and it updates fine. When I include all the fields, I'll get the Enter Parameter Value mesg. So it basically seems like it's not allowing all fields to be updated at the same time..

 

[arnelgp]

what is the field name? maybe i mistyped something. look at the [PART ID] on the second line, it has space?

 

[Pat Hartman]

Can you explain the "t." and "s."

They are called alias' and are a way of substituting a short name for your table name. When a query is lengthy, alias' make it easier to read. And given the poor construction of all your names will probably eliminate a lot of typos as well. Unless you are willing to also fix the column names to remove spaces and special characters, you're stuck with surrounding all the offending names with [] which to me always get confused with () so that may be the typo you are looking for.

 

[LisaC31]

Ok great! Thanks Arnel!! It looks like the Part Id field did have extra spaces in addition to an extra space in a couple of other fields in the source table.

I may update the field names to the proper naming convention but needed a quick fix to work with for now.

Thanks again, much appreciated!