Help with complex single-table UPDATE query

>I am trying to write an SQL UPDATE statement for an MSAccess table and
>am having some problems getting my head around it. Can anyone help?
>
>TableName: CustTransactions
>TransactionKey AutoNumber (Primary Key)
>CustomerID Long Integer (Non-unique index)
>AmountSpent Double
>CustSelected Boolean
>
>What I would like to do is, for all of the records in descending order
>of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE
>such that the sum of all the AmountSpent records is no greater than a
>specified amount (say $50,000).
>
>What I'm doing at the moment is a "SELECT * FROM CustTransactions WHERE
>CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
>through all the records until AmountSpent 50000, then continuine to
>loop through the remainder of the records setting CustSelected = FALSE.
>This works but is slow and inefficient. I just know it could be done in
>a single SQL statement with subqueries, but I can't figure it out.
>
>The closest I can get is:-
>
>UPDATE CustTransactions SET CustSelected = FALSE
>WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE
(((CustTransactions.CustSelected)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
>
>However, this mereley ensures only the top 50,000 customers by amount
>spent are "selected", not the top "X" customers who have spent a total
>of $50,000. I really need to replace the "SELECT TOP 50000" with some
>form of "SELECT TOP (X rows until sum(AmountSpent) =50000)".
>
>Is it even possible to achieve what I'm trying to do?
>
>Thanks in advance for any assistance offered!

I am trying to write an SQL UPDATE statement for an MSAccess table and
am having some problems getting my head around it. Can anyone help?
>
TableName: CustTransactions
TransactionKey AutoNumber (Primary Key)
CustomerID Long Integer (Non-unique index)
AmountSpent Double
CustSelected Boolean
>
What I would like to do is, for all of the records in descending order
of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records is no greater than a
specified amount (say $50,000).
>
What I'm doing at the moment is a "SELECT * FROM CustTransactions WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This works but is slow and inefficient. I just know it could be done in
a single SQL statement with subqueries, but I can't figure it out.
>
The closest I can get is:-
>
UPDATE CustTransactions SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE
(((CustTransactions.CustSelected)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
>
However, this mereley ensures only the top 50,000 customers by amount
spent are "selected", not the top "X" customers who have spent a total
of $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent) =50000)".
>
Is it even possible to achieve what I'm trying to do?

>
I am trying to write an SQL UPDATE statement for an MSAccess table and
am having some problems getting my head around it. Can anyone help?
>
TableName: CustTransactions
TransactionKey AutoNumber (Primary Key)
CustomerID Long Integer (Non-unique index)
AmountSpent Double
CustSelected Boolean
>
What I would like to do is, for all of the records in descending order
of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records is no greater than a
specified amount (say $50,000).
>
What I'm doing at the moment is a "SELECT * FROM CustTransactions WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This works but is slow and inefficient. I just know it could be done in
a single SQL statement with subqueries, but I can't figure it out.
>
The closest I can get is:-
>
UPDATE CustTransactions SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE
(((CustTransactions.CustSelected)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
>
However, this mereley ensures only the top 50,000 customers by amount
spent are "selected", not the top "X" customers who have spent a total
of $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent) =50000)".
>
Is it even possible to achieve what I'm trying to do?
>
Thanks in advance for any assistance offered!
>
really need to replace the "SELECT TOP 50000" with some
>form of "SELECT TOP (X rows until sum(AmountSpent) =50000)".