Query Assistance

[susieqtotallynew]

Hi, all,
I'm hoping you'll help me now as you have before.
I have two tables--table A and table B. Table A has information that exists in a current database. Table B has information that may be used to update the information in the current database. In some cases, information in a field in table A could be updated by the information in table B. In other cases, there IS no information for a field in table B and the information should stay the same as it is currently in Table A. Each of these tables contains a date when the information was updated.
How would you formulate a query that would do the following: If information in field B is blank, ignore it. If information in field B is not blank, compare it to information in field A. If the information in field B is different than the information in field A AND the date of the information in field B is more recent, keep the information in field B. If the information in field A and B is different and the information in field A is more recent, keep the information that is in field A. I should note that this information will be used as the basis for an import to the database, not to make changes to it directly.
Thank you in advance for your help.

 

[PHV]

What is the relationship between table A and table B ?

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

 

[susieqtotallynew]

They share an ID number.

 

[Andrzejek]

Could you just loop thru table B, compare the Date of the records in table A and B with the same ID number and update table A's record if needed?

Have fun.

---- Andy

 

[PHV]

UPDATE [Table A] A INNER JOIN [Table B] B ON A.ID = B.ID
SET A.FieldA = B.FieldB
WHERE Trim(B.FieldB & "") <> "" AND B.[date updated] > A.[date updated] AND B.FieldB <> Trim(A.FieldA & "")

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