UPDATE Query puzzler

[BrockWade]

I have two tables Bank & DTC... I need to update the bottom table (DTC) so that if a match is made both for the Store field and the Amount field that row gets updated with the "DTCdate" value in the bottom table is changed to match the "DateBank" value in the top table... any clues? Subquery needed?

DateBank Store Amount
9/9/2013 1 3,489.54
9/5/2013 1 3,250.81
9/9/2013 1 3,230.63
9/16/2013 1 3,070.90
9/23/2013 1 2,778.21
9/26/2013 1 2,744.02


9/20/2013 2 2,692.37
9/16/2013 2 2,642.12
9/23/2013 2 2,628.62


DTCdate Store Amount
9/6/2013 1 3,489.54
9/7/2013 1 3,230.63
9/11/2013 1 3,070.90
9/20/2013 1 2,778.21
9/24/2013 1 2,744.02
9/18/2013 2 2,692.37

[June7]

Why do you need to do this? I see risk. What if there is more than one record with the same Amount for the same store?

Seems to be a poor db design. What do these tables represent?

[Dal Jeanis]

Here's your basic update query if there cannot be duplication of Store/Amount Combinations.

UPDATE DTC AS TD, Bank AS TB
SET TD.DTCDate = TB.DateBank
WHERE ((TD.Store = TB.Store)
AND (TD.Amount = TB.Amount));

If you know that there is a certain number of days lag typical, say 1-7 days, then you could do this to limit your exposure

UPDATE DTC AS TD, Bank AS TB
SET TD.DTCDate = TB.DateBank
WHERE ((TD.Store = TB.Store)
AND (TD.Amount = TB.Amount)
AND (TB.DateBank BETWEEN DateAdd("D",1,TD.DTCDate) AND DateAdd("D",7,TD.DTCDate))) 
;

You might also add a beginning and ending date to the update, so you wouldn't be constantly recalculating last years' data.