Im trying to total the client invoices and post it into the client table

This works and returns the total

SELECT SUM(Total) FROM Invoices WHERE Client_ID = 123

this does not work

UPDATE Clients SET Total_Sales = (SELECT SUM(Total) FROM Invoices WHERE Client_ID = 123)
WHERE ID = 123

Aaron


>this does not work
>
>UPDATE Clients SET Total_Sales = (SELECT SUM(Total) FROM Invoices WHERE Client_ID = 123)
>WHERE ID = 123

DBISAM just doesn't support that syntax. Its a long time since I used DBISAM so I may be wrong, or there may be a better way but I'd suggest a script along the lines of

select 'total_sales' as id, sum(total) as totals where client_id = 123 into memory\fred  from invoices;
update clients set total_sales = totals from clients
left outer join memory\fred as fred on id = _total_sales;
drop table memory\fred;

Roy Lambert [Team Elevate]