Help with complex single-table UPDATE query

[Slower Than You]

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!
--
SlowerThanYou

[kingston via AccessMonster.com]

I think your subquery should look something like:

SELECT TOP X CustomerID FROM CustTransactions WHERE (DSum("[AmountSpent]","
[CustTransactions]","[CustomerID]=" & [CustomerID]) 50000);

However, since this uses an aggregate function, Access might not let you
update any records. You could use this query to create a temporary table and
then join on [CustomerID] in a regular update query. HTH

Slower Than You wrote:

>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!

--
Message posted via http://www.accessmonster.com

[pietlinden]

Slower Than You wrote:

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?

you can't use a parameter to specify the number of top values to return
in a SQL query. (I know, because I asked this question several years
ago...) You could create a form to get the number of values you want
returned and then modify the querydef's SQL statement.

What it sounds like you're trying to do is something like a
check-processing scenario, where you have something like an account
balance and then a list of checks to clear against the account, and you
want to clear all the checks you can up to the point you hit the
"insufficient funds" problem. Unless I'm completely clueless, you'd
have to open an updatable recordset of outstanding checks and then
process

dim rs as DAO.Recordset
set rs=...
do until rs.EOF Or curBalance < rs.Fields("CheckAmount")
'--decrease amount of available funds
curBalance=curbalance - rs.fields("CheckAmount")
'---set the clear date/flags
rs.Edit
rs.Fields("ClearDate")=Now
rs.Update
rs.MoveNext
loop

[Mike Gramelspacher]

In article <11****************@proxy00.news.clara.net>,
no.way@jose says...

>
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)".

It seems that this is asking for both a ranking and a running
sum and
may require two queries.

SELECT CustTransactions.TransactionKey,
CustTransactions.CustomerID,
CustTransactions.AmountSpent,
CustTransactions.CustSelected,
(SELECT COUNT(* )
FROM CustTransactions AS ct2
WHERE CustTransactions.AmountSpent < ct2.AmountSpent
OR (CustTransactions.AmountSpent = ct2.AmountSpent
AND CustTransactions.TransactionKey <=
ct2.TransactionKey)) AS Rank
FROM CustTransactions
WHERE CustTransactions.CustSelected = True;

UPDATE CustTransactions SET CustSelected = FALSE
WHERE TransactionKey = ANY (SELECT a.TransactionKey
FROM Ranked_Customer_Transactions AS a
INNER JOIN Ranked_Customer_Transactions AS b
ON b.Rank <= a.Rank
GROUP BY a.TransactionKey
HAVING SUM(b.AmountSpent) <= [Enter dollar amount:]);