Can this be done using an Update query 1

[HitechUser]

I've searched for a solution, but I am unable to find one with enough detail. I hope someonce can help. I may be pushing my luck since I was fortunate to have another question answered last week. Anyway... here's a sample of my data (Using Access 2000):

Tables before

tblPartsMaster
PARTID User Date Vendor Location
CV0001 1695 10/14/2003 Intel Bin1
CV0002 1695 10/15/2003 AMD Bin2
CV0003 1695 xx/xx/xxxx Dell xxxx
DX0001 1696 xx/xx/xxxx xxxx xxxx
DX0002 1697 xx/xx/xxxx xxxx xxxx
FX0001 1697 xx/xx/xxxx xxxx xxxx
GX0002 1697 xx/xx/xxxx xxxx xxxx
GX0003 1697 xx/xx/xxxx xxxx xxxx

tblPartsUpdate
PARTID User Date Vendor Location
CV0001 10/31/2004 AMD ""
CV0002 1695 AMD " "

Table after

tblPartsMaster
PARTID User Date Vendor Location
CV0001 1695 10/31/2004 AMD Bin1
CV0002 1695 10/15/2003 AMD Bin2
...

Can an Update Query be created to ONLY update the columns where the IS a valid value in "tblPartsUpdate"? I do not want to change the "tblPartMaster" column's value if the "tblPartsUpdate" column has one of the following.

1. - NULL
2. - "" (zero length)
3. - " " (one or more blanks)

Basicaly I need to update each row's columns if a valid value (any value) is present.

If this can not be done with an update query can someone please provide some VB code to perform the same result.

Thanks so much.

 

[JonFer]

Use IIf to check for the presence of a good value. If so, update the master from the "udpates" table. If not, update the master table to the value already there. There are different IIf conditions for dates versus text fields. Here is just the SET clause:

Set FieldText = IIf(Trim(nz([tblPartsUpdate].[FieldText],""))="",[tblPartsMaster].[FieldText],[tblPartsUpdate].[FieldText]) ,
FieldDate = IIf([tblPartsUpdate].[FieldDate] Is Null,[tblPartsMaster].[FieldDate],[tblPartsUpdate].[FieldDate])


John

http://www.interautomateconsulting.com

 

[OhioSteve]

You could perform this task with one monster update query. However, I would do it with four small queries. Here is an example:

UPDATE Table1
LEFT JOIN Table2 ON Table1.f1 = Table2.f1 SET Table1.f2 = [table2].[f2]
WHERE (((Trim([table2.f2])) Is Not Null));

Table1= tblPartsMaster
Table2= tablPartsUpdate
f1= PARTID field
f2= USER field

Remember that you could put the four queries into a macro. So it would be just as easy to run four queries or one.

 

[JonFer]

OhioSteve - wouldn't you use an INNER JOIN instead? Your WHERE condition allows the "" and " " values to be applied.

John

http://www.interautomateconsulting.com

 

[OhioSteve]

Jonfer,

When I read your post I agreed with you. Then I retested my old query and I did not see a problem. Nonetheless, if HiTechUser is reading this use "inner join".

Steve

 

[HitechUser]

OhioSteve & JonFer,

Thanks for your responses. I have a few questions.

1. JonFer, your first reply. Can this string be placed into the update query? Or is your example just for VB? Is there something else missing?

2. OhioSteve, your solution of multiple queries is okay but, there are actually 12 columns that (potentially) need to be updated. I was trying to keep this post's example short. My concern with so many queries is, what if a failure occurs halfway thru the process?. Since I am only updating columns and not the entire row, recovery may be a problem. Maybe I can rerun the batch? Also does your "where" clause handle Blanks or Zero length values?

Once again thanks for all the help

 

[JonFer]

OhioSteve - The Left Join is negated by putting a criteria on the "right" table so the result is the same as doing an inner join but with the extra Left Join processing.

HitechUser - You can use the IIf() inside an update query.

John

http://www.interautomateconsulting.com

 

[HitechUser]

JonFer,

I was able to use your IIf example in an update query. I placed one IIf for each field that needed to be updated. Viola! it worked. I must have done something wrong during my first attempt (it seems the expression builder inserted some control characters). I am now able to update an entire row at once! The whole update now occurs using a single update query.


OhioSteve,

Thanks for your suggestion. I will retain your code example, as I realize that the application will become more envolved (don't they all) and it may soon require multiple queries.

JonFer and OhioSteve,

Once again thanks to the both of you for your quick and accurate responses. SERIOUSLY!!!!