excel set up, how to do in access

[Dal Jeanis]

Create this query and run it against your data and see if it gives you reasonable results for the beginning and ending positions and for the prior date. If it works correctly, then we can use it as a base to build your desired query.

SELECT T1.ClientID, T1.MonthlyDate, T1.TradePositionX,
    (SELECT MAX(T3.MonthlyDate) 
    FROM Accounts AS T3
    WHERE T3.ClientID = T1.ClientID
    AND T3.MonthlyDate < T1.MonthlyDate) AS PriorDate,
   (SELECT SUM(T2.TradePositionX) 
    FROM Accounts AS T2
    WHERE T2.ClientID = T1.ClientID
    AND T2.MonthlyDate <= T1.MonthlyDate) AS NetPosition,
    NetPosition - T1.TradePositionX AS BegPosition
FROM Accounts AS T1
ORDER BY T1.ClientID, T1.MonthlyDate;

So, productID isn't the right thing. If a client holds several different positions, what is the field in the Accounts table that tells which one he is buying and selling?

[Dal Jeanis]

That SQL should be close, for clients who only hold one item and who don't buy/sell more than one time per day.

The records have to have an explicit order, to deal with multiple transactions per day. As general practice, I always give an autonumber key to every transaction table, because otherwise you can't hang on to a unique reference to that transaction, which complicates updating and comparison. Use sequentially assigned autonumber, not the random option.

Take the following SQL, replace ProductID with whatever field says what the client is buying or selling (maybe InvestmentID?), replace Autokey with the name of the the autonumber field on the Accounts table

SELECT T1.ClientID, T1.ProductID, T1.MonthlyDate, T1.Autokey, T1.TradePositionX,
    (SELECT MAX(T3.MonthlyDate) 
    FROM Accounts AS T3
    WHERE T3.ClientID = T1.ClientID
    AND T3.ProductID = T1.ProductID
    AND (T3.MonthlyDate < T1.MonthlyDate OR
        (T3.MonthlyDate = T1.MonthlyDate AND
         T3.AutoKey < T1.Autokey))) AS PriorDate,
   (SELECT SUM(T2.TradePositionX) 
    FROM Accounts AS T2
    WHERE T2.ClientID = T1.ClientID
    AND T2.ProductID = T1.ProductID
    AND T2.MonthlyDate <= T1.MonthlyDate
    AND (T2.MonthlyDate < T1.MonthlyDate OR
        (T2.MonthlyDate = T1.MonthlyDate AND
         T2.AutoKey <= T1.Autokey))) AS NetPosition,
    NetPosition - T1.TradePositionX AS BegPosition
FROM Accounts AS T1
ORDER BY T1.ClientID, T1.ProductID, T1.MonthlyDate;

[mike02]

It works, but it has me insert a parameter for Beg Position. How can I automaticall have it be 0? I tried setting the criteria, however it will then ask me NetPosition as a parameter.

I also have another query which calcs the row before but It is just a single value:

IE>

Starting Assets.....CASH.....COB
insert my self.......10,000....30,000
30,000

Same Idea with ClientID and MonthDate.

would I be able to use the same code to set it up, but adjust this line:

(SELECT SUM(T2.TradePositionX)     FROM Accounts AS T2   
 WHERE T2.ClientID = T1.ClientID    
AND T2.ProductID = T1.ProductID    
AND T2.MonthlyDate <= T1.MonthlyDate    
AND (T2.MonthlyDate < T1.MonthlyDate OR  (T2.MonthlyDate = T1.MonthlyDate 
AND         T2.AutoKey <= T1.Autokey))) AS NetPosition,    NetPosition - T1.TradePositionX AS BegPosition

Thanks for the help!

[Dal Jeanis]

Try this:

(SELECT SUM(T2.TradePositionX)  AS NetPosition, 
    SUM(T2.TradePositionX) - T1.TradePositionX AS BegPosition
    FROM Accounts AS T2
    WHERE T2.ClientID = T1.ClientID
    AND T2.ProductID = T1.ProductID
    AND T2.MonthlyDate <= T1.MonthlyDate
    AND (T2.MonthlyDate < T1.MonthlyDate OR
        (T2.MonthlyDate = T1.MonthlyDate AND
         T2.AutoKey <= T1.Autokey)))

[mike02]

How do i set it up for a value. not an expression.. IE>

Select VALUE(T2.TradePositionx)

I just want the value of the row before in that column. is that possible?

Thanks

[Dal Jeanis]

You cannot get to the row before - you have to collect whatever data you need into the row itself, which is what all those subselects were for.

Or did you mean that TradepositionX is stored as a text field????

[mike02]

The row before is the sum. therefor i could do this: (Select(Sum([Cash]) and so on..

But the cash isnt calculating. Is it because it is not a field in a table, but an expression in the query itself? if so, how do i get around that?

And.

I want to find values for more then one. I need to do it for 2, but it says i can only find one.