Updating too many rows 1

[bikerted]

My update query performs correctly, but I'm concerned about why when I test it with the warnings set as true, the number of rows referred to is zillions more than are actually being updated - well actually 146 when two were updated and 5183 when about 70 were updated!). The following is the SQL:

UPDATE BankingReturnqry INNER JOIN Receipts ON BankingReturnqry.PayingInNo = Receipts.PayingInNo SET Receipts.VATcode = [Forms]![BankingReturnfrm]![Combo29]
WHERE (((Receipts.VATcode)=[Forms]![BankingReturnfrm]![Combo33]) AND ((Receipts.IncomeType)=[Forms]![BankingReturnfrm]![Combo30])) OR (((Receipts.IncomeType)=[Forms]![BankingReturnfrm]![Combo30]) AND (([Forms]![BankingReturnfrm]![Combo33]) Is Null));

BankinReturnqry is based on fields in the table Receipts - Is this where the problem lies??

I'd be most grateful for ant assistance.

Ted.

 

[bikerted]

I sorted this myself after using Microsoft Help on update queries. It advised that, when more than the requisite number of rows is trying to update, there are a number of reasons/possible solutions - one of which is duplicate values, and changing the query property to unique values. This boggled my mind, because the unique value property was not available on my query. However, I selected Yes for the unique records property and it worked. The correct number of rows was shown with the set warnings property not set to false, and these were the only rows updated.

The only problem is I don't understand how 5183 rows reduced down to 71! I would value anyone's input to further my knowledge.

Ted.

 

[formerTexan]

Ted,

Try creating a SELECT query with the same fields and WHERE clause. It should return your 5183 records. Then have a look at those records and see what is going on.

Cheers,
Bill

 

[bikerted]

Thank you Bill.

Will give it a go.

Ted