Update with JOIN but only on Top 1 ?

[qwertyjjj]

I'm trying to update a table via a join with the current query:

UPDATE zarageddebt_CURR INNER JOIN baddebt_CURR ON baddebt_CURR.DocNo = zarageddebt_CURR.DocNo SET zarageddebt_CURR.[Bad Debt] = baddebt_CURR.[Bad Debt];

Unfortunately, I have just discovered that the relationship is not always 1 to 1 (although it is in 99% of cases). In those where, it isn't and the destination table has, say, 3 records with the same DocNo, I would like to update only the top record.

Can this be done in the join? I don't think this will work:
UPDATE zarageddebt_CURR INNER JOIN baddebt_CURR ON TOP 1 baddebt_CURR.DocNo = zarageddebt_CURR.DocNo SET zarageddebt_CURR.[Bad Debt] = baddebt_CURR.[Bad Debt];

 

[qwertyjjj]

bump .....

 

[stopher]

What do you mean by "the top record" ? If you have three records, by what definition are you deciding which one is top ? The order by which Access will present the list to you isn't static so you can't simply say the top in the list. I suggest you consider something like the primary key or the most recently updated record if you have that info in your table. If you don't consider this then you have no way or guaranteeing that the last record you updated will be the one you update next time.

You need something like this:

UPDATE zarageddebt_CURR INNER JOIN baddebt_CURR 
ON zarageddebt_CURR.DocNo=baddebt_CURR.DocNo 
SET zarageddebt_CURR.[Bad Debt] = baddebt_CURR.[Bad Debt]
WHERE zarageddebt_CURR.[Bad Debt] In
                 (Select Top 1 [Bad Debt]
                 From zarageddebt_CURR
                 Where zarageddebt_CURR.[DocNo]=[baddebt_CURR].[DocNo]
                 Order By [Bad Debt] Desc);

This isn't quite right because as I said, there's no way to predict which record the top one will be.

As an aside, why are you wanting to hold the same data in two tables ?

Stopher