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: CustTransaction s
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 "AmountSpen t" where "CustSelect ed = 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 CustTransaction s 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 CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=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 CustTransaction s WHERE (DSum("[AmountSpent]","
[CustTransaction s]","[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: CustTransaction s
TransactionK ey 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 "AmountSpen t" where "CustSelect ed = 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 CustTransaction s 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 CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=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: CustTransaction s
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 "AmountSpen t" where "CustSelect ed = 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 CustTransaction s 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 CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=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
"insufficie nt 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("Chec kAmount")
'--decrease amount of available funds
curBalance=curb alance - rs.fields("Chec kAmount")
'---set the clear date/flags
rs.Edit
rs.Fields("Clea rDate")=Now
rs.Update
rs.MoveNext
loop

[Mike Gramelspacher]

In article <11************ ****@proxy00.ne ws.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: CustTransaction s
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 "AmountSpen t" where "CustSelect ed = 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 CustTransaction s 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 CustTransaction s SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransaction s WHERE
(((CustTransact ions.CustSelect ed)=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 CustTransaction s.TransactionKe y,
CustTransaction s.CustomerID,
CustTransaction s.AmountSpent,
CustTransaction s.CustSelected,
(SELECT COUNT(* )
FROM CustTransaction s AS ct2
WHERE CustTransaction s.AmountSpent < ct2.AmountSpent
OR (CustTransactio ns.AmountSpent = ct2.AmountSpent
AND CustTransaction s.TransactionKe y <=
ct2.Transaction Key)) AS Rank
FROM CustTransaction s
WHERE CustTransaction s.CustSelected = True;

UPDATE CustTransaction s SET CustSelected = FALSE
WHERE TransactionKey = ANY (SELECT a.TransactionKe y
FROM Ranked_Customer _Transactions AS a
INNER JOIN Ranked_Customer _Transactions AS b
ON b.Rank <= a.Rank
GROUP BY a.TransactionKe y
HAVING SUM(b.AmountSpe nt) <= [Enter dollar amount:]);