excel set up, how to do in access

[mike02]

Hello:



I have a query that is providing a bunch of calculations for me.

In excel I have this:

Beg....Add/Sub....ClientID......MonthlyDate
0........5...............1.............02/01/2013
5........6...............1.............31/01/2013
11......-5..............1.............28/02/2013
6 and so on...........

What i am trying to figure out is how to set up a formula in the Beg to have it calc after the first one.. So it would Be sum(Beg:Add/Sub)

Now I do Have a clientID which is the same and a Monthly Date.

how would this be set up? Would It be Dsum? and if so how would that work?

Thanks,

[Dal Jeanis]

SELECT t1.ClientID, T1.MonthlyDate, T1.AddSub, T2.NetSoFar, T2.NetSoFar - T1.AddSub AS BEG
FROM MyTable AS T1
INNER JOIN
   (SELECT T2.ClientID, SUM(T2.AddSub) AS NetSoFar
    FROM MyTable AS T2
    WHERE T2.ClientID = T1.ClientID
    AND T2.MonthlyDate <= T1.MonthlyDate)
ON T2.ClientID = T1.ClientID

[mike02]

can you explain how this code works? i will have to do many more calcs like this, and it would be nice to do it on my own, which will help my programming ability in the long run.

Many thanks,

[Dal Jeanis]

The general subject name is "SubSelect" or "SubQuery", which you can google for lots of info.

Specifically, this is a correlated subquery. I'm connecting the same table to itself, so I need to alias the table, and I might as well do it twice to save typing. By the way, that syntax might not be great, I haven't tried it. Warning: correlated subqueries can be very machine intensive.

The first thing to understand is that each record in T1 will result in a search in T2 for all records that match the conditions. The conditions are, that the company number is the same, and the date is before or equal the current record. Once those records have been selected, the subselect will SUM the AddSub field and return the result aliased AS NetSoFar.

The client 1 record in T1 for 02/01/2013 will find one   record  in T2 that is <= 02/01/2013 and T2.NetSoFar is 5
The client 1 record in T1 for 31/01/2013 will find two   records in T2 that are <= 31/01/2013 and T2.NetSoFar is 11
The client 1 record in T1 for 02/01/2013 will find three records in T2 that are <= 28/02/2013 and T2.NetSoFar is 6

BEG is just T2.NetSoFar minus T1.AddSub.

The code could have been changed to a < instead of <= and the Sum itself would generally have resulted in the same amount as BEG, but I didn't want to deal with coding around the issue of the first date for each company. Thus, thus way was slightly more elegant.

[Dal Jeanis]

According to this page http://msdn.microsoft.com/en-us/libr...ffice.11).aspx it looks like the explicit join was unnecessary and the syntax should be:

SELECT t1.ClientID, T1.MonthlyDate, T1.AddSub,
   (SELECT SUM(T2.AddSub)
    FROM MyTable AS T2
    WHERE T2.ClientID = T1.ClientID
    AND T2.MonthlyDate <= T1.MonthlyDate) AS NetSoFar,
    NetSoFar - T1.AddSub AS BEG
FROM MyTable AS T1
ORDER BY t1.ClientID, T1.MonthlyDate;

That query feels much better to me.

[mike02]

i will do some more research, hopefully that helps,

Do you by chance have an example DB with this set up in it?

Thanks for the input,

[mike02]

Here Is my SQL:

SELECT Accounts.ClientID, Accounts.ProductID, Accounts.MonthlyDate, MonthEnd.InvestmentID, MonthEnd.AdjClose AS LastMTM, [NEEDED FORMULA] AS [Beg Position], Accounts.TradePositionX AS [Add/Sub Posn], 




Accounts.TradePriceX AS [Add/Sub Price], Accounts.BrokerageFee, MonthEnd.AdjClose AS MTMPrice, Accounts.XRate, [Formula1] AS [Fee], [LastMTM]*[Beg Position] AS [Asset Value Beg], 




[Formula2] AS [Assets Add/Sub], ([Beg Position]+[Add/Sub Posn])*[MTMPrice] AS EOMAssetValue, [EOMAssetValue]-[Assets Add/Sub]-[Asset Value Beg] AS [AssetsInc/Dec]


FROM MonthEnd INNER JOIN Accounts ON MonthEnd.MonthlyDate = Accounts.MonthlyDate


WHERE (((MonthEnd.InvestmentID)=3));

I have tried a bunch of ways but cannot figure out how to fit it in the sql,

I have never worked with sqls before, I have just done enough VB, where i can write basic things, and kind of understand it. My syntax is brutal.
The bolded is where i need that formula to take place.


Thanks for the help,

[Dal Jeanis]

Let's back up a step. Please give me an English description of the following
Accounts.MonthlyDate
Accounts.TradePositionX
Accounts.TradePriceX
Accounts.BrokerageFee
Accounts.XRate
MonthEnd.MonthlyDate
MonthEnd.InvestmentID
MonthEnd.AdjClose

[mike02]

Accounts.MonthlyDate- each date a new execution occurs
Accounts.TradePositionX- The change in position held
Accounts.TradePriceX- the execution price
Accounts.BrokerageFee - the fee per execution
Accounts.XRate- the rate per execution
MonthEnd.MonthlyDate- Date which corresponds with accounts date, but matches to the ADJ close
MonthEnd.InvestmentID- The type of invesment selected
MonthEnd.AdjClose- the number from the website, it corresponds with the date in this table.

[mike02]

Here is what i have tried to write from what my research has brought up:

(Select [Beg Position]+[Add/Sub Posn] From (QueryName) Note: Beg Posn Is a calculated field therefore not in the table Accounts
Where Accounts.MonthlyDate = MonthEnd.MonthlyDate (How do I make this 1 Row above)

Now, is client ID necessary? I have it to search so it brings up all records that match client ID, but is it necessary to create the where clause off of it, since it is always the same:S

In a Sub Query, where would I insert it in a regular SQL query? and how to a Make the Beg Position (answer insert in the same column as Beg Position)

[Dal Jeanis]

When I'm developing code or SQL, I get to the correct result by telling myself, in English, what I'm trying to do. That way, when I review the SQL, I can verify the relationships between entities or values against what makes sense for the real-world situation.

I always start by making sure that I thoroughly understand the meaning of each field on which calculation will occur.

Sorry, I don't mean to be dense, but I need enough descriptive words to be sure that I'm not making the wrong assumptions.

So, backing up, let me make sure I understand the two tables:

1) The Accounts table is a table of daily transactions, right? What entity does a record on the table actually represent?

2) The Monthend table is ... also daily? What entity does a record on the table actually represent?

And now let me make sure I understand a couple of the fields:

3) I assume BrokerageFee is in Dollars, and XRate is in percentage (where 0.005 would be one-half of a percent)

4) Is Brokeragefee in addition to the rate, or is it the calculated value of the rate, or what? Is Brokeragefee the fee the customer pays you, or the fee your company pays an outside vendor?

5) As far as AdjClose, I'm still lost. A number which corresponds with a date. One of your aliases on it includes the word "price". So, is this the execution price for the sale? Or is it the per-share value of the asset at end of day, or beginning of the prior day?

[mike02]

1. Yes, the accounts table is all transactions. Each record, represents the transactions on the given day for the client
2. MonthEnd table holds all AdjClose Values, for all different investments.
3. BrokerageFee is in dollars, It is a cost on my part,
4. Adj Close is the close price of a particular day, which matches the MonthlyDate in that table.


The query is set up as I am wanting it to be. the issue I am having is only with the [Beg Position].

Beginning Position is an expression and it is not a field in either table.

Note: Beg Position always starts as zero in the first Row:

Eg.

Beg Position.....Add/Sub Posn.....
0....................4....
4....................5......
9...................-6.....
3...............And So On

How would I set up the subquery to but the data in the beg posn with the formula?

If you believe i have miss set up my DB, or query please let me know. as i am always taking input

Thanks!

[Dal Jeanis]

Still gathering information.
1) if a customer makes two transactions on a single day, he has two different Accounts records for that day, right?
2) MonthEnd table contains the closing price for each day for each Investment
3) Do the Accounts table and the MonthEnd table each have an autonumber Primary Key? I mean an indexed autonumber field that uniquely identifies each record.

[Dal Jeanis]

Shouldn't Accounts.ProductID = MonthEnd.ProductID as well?

[mike02]

1. Yes, there would be. the date stamp would be the same. Should I include a time stamp?
2. yes
3. Monthend table doesnt have an primary key, It does have an Autonumber ID i just do not have a primary key setup

Month End does not have a Product ID. Product id is a foreign key in Accounts, from Products Table.