Update Query that clears fields that are not in the new data 1

[Renasant]

I have an Microsoft Access update query that I run each month with no real issues. We download data from our mainframe (we are a bank) to excel. The excel worksheets are then used as linked tables to update parts of the access database.

The only problem is when something becomes blank. Assume branch number 5 or 322 is no longer used by our bank or simply has zero data this month. The mainframe output would not even have 5 or 322 so my linked table would not have 5 or 322.

After the update query runs, my access table would still show the data for 5 and 322 from the last time that there was data for them in the linked table. How do I set fields within a record to 0 or null if the linked key is not even in the linked date table?

Right now I am considering two update queries. One to set all the updated values to zero and one to update as it does now.

NOTE: This is a Microsoft Access question, not a sql question. I have no sql skills.

 

[PHV]

What is the actual SQL code of your Update query ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Renasant]

UPDATE [Linked Portfolio Report] INNER JOIN Hierarchy ON [Linked Portfolio Report].CC = Hierarchy.Businessunitid SET Hierarchy.[No notes] = [linked portfolio report].[# notes], Hierarchy.Balance = [linked portfolio report].[lgr balance];

My original post left out that this I am running Access 2007, but this is an Access 2003 database. I cannot update to the 2007 database type since I am not a sys-admin on my PC and the 2007 database formats really need you to be an admin on your machine.

 

[PHV]

What about this ?

UPDATE [Linked Portfolio Report] [!]RIGHT[/!] JOIN Hierarchy ON [Linked Portfolio Report].CC = Hierarchy.Businessunitid
SET Hierarchy.[No notes] = [linked portfolio report].[# notes], Hierarchy.Balance = [linked portfolio report].[lgr balance];

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Renasant]

Thanks! That did it.