Table: Data1 , Fields: TransactionID, TDate, Invested, Amount, Percent
First record contains Tdate as Start Date and Invested as beginning invested.
Want to create query to use invested in first record and subtract Amount in second and * Percent and to do this for all subsequent records which have data. 300 odd records where some data may change occasionally.
Have a query which I cannot get to work (syntax error)
SELECT TransactionID, TDate, Amount, (SELECT Sum([X].[Amount]*X.[Percent])
AS FROM Data1 AS X WHERE X.TDate <= Data1.TDate) AS Invested FROM Data1
anyone help please
First record contains Tdate as Start Date and Invested as beginning invested.
Want to create query to use invested in first record and subtract Amount in second and * Percent and to do this for all subsequent records which have data. 300 odd records where some data may change occasionally.
Have a query which I cannot get to work (syntax error)
SELECT TransactionID, TDate, Amount, (SELECT Sum([X].[Amount]*X.[Percent])
AS FROM Data1 AS X WHERE X.TDate <= Data1.TDate) AS Invested FROM Data1
anyone help please