Subquery to find previous value

[mike02]

Hello,

I have created this sub query:

(SELECT SUM(Cash)     
FROM qryAccountSetup AS T2      
WHERE T2.ClientID = [qryAccountSetup].ClientID   
AND T2.MonthlyDate < [qryAccountSetup].MonthlyDate) AS PriorCash,

How do you change this to allow multiple values. I looked up subqueries online and it said to nest a second sub for the where clause.... so ive been trying this..

(SELECT (Cash)     
FROM qryAccountSetup AS T2      
WHERE 
(Select  (SELECT MAX(T3.MonthlyDate)      
FROM qryAccountSetup AS T3     
WHERE T3.ClientID = qryAccountSetup.ClientID     
AND T3.MonthlyDate < qryAccountSetup.MonthlyDate)) AS PriorCash,

But i keep getting error.



What i am trying to do is get the previous cash value....
For now, a good indicator is MonthlyDate, as only one date per client will exist. Something can only happen once in a day....IE.

ClientID..MonthlyDAte
1...........03/01/2013
1...........31/01/2013
1...........01/02/2013
1...........and so on.

There can be multiple dates, however the clientID would not match then.... Does this make sense? There is also a PositionID... This is an autonumber field in its respective table.. I have that so i can use it for domain purposes in the future.

[rpeare]

you're trying to do a running sum, don't do that in a query if you can avoid it, rather do it in a report where you have the option to make a field a running sum and have it do the work for you. Running sums in queries are extremely inefficient.

[mike02]

Im not trying to do a running sum, I used the sum function just to try and get a value to appear and make sure It worked.
Here is what i want to do:

ClientID.....MonthlyDate.....COBAssets..ValueRetur nedfromSUBQUERY
1.............02/01/2013......100,000........
1.............31/01/2013......120,000........100,000
1.............28/02/2013......110,000........120,000
1.............31/03/2013......90,000..........110,000

[rpeare]

can you provide a sample database (2007 or earlier please) just an example of the table with some mock up data you're using and the query as you have it right now.

[mike02]

Okay, It is in qryAccountSummary, every other query just sets it up.

Here is the attached File.

the previous COBAssets = Starting Assets,

You will see the subquery i have set up in qryAccountSummary as a sum function, how ever I want it to take the previous COBAssets VAlue...

[rpeare]

Your query is correctly calculating the previous date so can you just do something like I have shown in the query qryPositionsCurrPrev


Database3.zip

[mike02]

yes that is what i am wanting. When I adjust your queries to have it COBAssets instead of BrokerageFee, I get and Error, Invalid Argument To function.

All I adjusted is the table used in qryPositionBase, to qryAccountSetup instead of Positions, and then I updated LEFT JOIN qryAccountSetup As current, and previous positions instead of Positions. That way I can use the field I need.

Part of the SQL:

FROM 
(qrySummaryCalcs LEFT JOIN qryPositionBase AS Previouse_Position
ON (qryPositionBase.PriorDate= qrySummaryCalcs.MonthlyDate) 
AND (qryPositionBase.ProductID = qrySummayCalcs.ProductID) 
AND (qryPositionBase.ClientID = qrySummaryCalcs.ClientID)

What is The qryPartUnion There for?

Thanks for the help,

[rpeare]

I'll take a look at the db when I have a chance, the qrypartunion was there because your table is not normalized you've got data stored horizontally rather than vertically and I wanted to see the results of your queries a through f all at once.

[mike02]

I tried setting it up like that, But i had no luck making it easy to input, By having it horizontal i find it easier to input the values for A, B, C and so on.. You would have to select Investment ID, each time.

Thinking about it now, and looking at it. It would be much easier to add new investments into the mix... Should I be re-structuring this to set it up like this?

Is there a quicker way of inputing the data... IE> using a tabbed form for inputting them... Each tab is a different investment... Is that possible?


thanks,

[mike02]

When I set it up to have the data normalized, and use the InvestmentID to distinguish the data... How ever i cannot get it to sort the investment ID, CLientID, and MonthlyDate...
It will only sort on either two of them.. The biggest thing is the subqeury, NetPosition Is summing All the data, Not just set to that specific investment...Youd see it in qry1, for example. The Net Position when all data is in it. doesnt Sum based on each investment.

I have added in:

AND T2.InvestmentID = T1.InvestmentID

but it prompts me to input it in manually...

[rpeare]

can you post your full SQL code you're trying right now.

Personally I prefer a fully normalized table.

In your example they are limited to a maximum of 6 funds and each record takes

In a normalized table they can have an unlimited number of investments or only one, the other limitation of your current design is that if the person's only involved in one investment you're storing a lot of empty data.

Setting up a data entry form for a normalized structure isn't that hard to do if you want to pursue that, but let's try to solve your original issue unless you're going to pursue the normalized structure.

[mike02]

Ok, I will set it up Normalized, just so I can add in new funds easier... Now, Here is the SQL, to my current Issue:

SELECT qryPositionBase.ClientID, qryPositionBase.ProductID, qryPositionBase.MonthlyDate, qryPositionBase.PriorDate, Current_Position.COBAssets, Previous_Position.COBAssets




FROM 
(qryPositionBase LEFT JOIN qryAccountSetup AS Current_Position 
ON (qryPositionBase.MonthlyDate = Current_Position.MonthlyDate) 
AND (qryPositionBase.ProductID = Current_Position.ProductID) 
AND (qryPositionBase.ClientID = Current_Position.ClientID)) 


LEFT JOIN qryAccountSetup AS Previous_Position 
ON (qryPositionBase.PriorDate = Previous_Position.MonthlyDate) 
AND (qryPositionBase.ProductID = Previous_Position.ProductID) 
AND (qryPositionBase.ClientID = Previous_Position.ClientID)


ORDER BY qryPositionBase.ClientID, qryPositionBase.MonthlyDate;

In your provided DB you have brokeragefee doing exactly what i was wanting to do, but for some reason I have not been able to make it work with a query instead of a table. I want to see the previous COB Assets,

[rpeare]

that's basically the query I gave you, it's connecting to your POSITIONS table with two different aliases. Current_Position and Previous_Position, your COB for qryA twice (preferably with two different aliases) one to the previous date one to the current date (with the other two link fields as well, similar to the way I've linked to the position table) then perform your cob calculation, in essence you'd be linking each of your qryA through qryF twice into your query then performing your COB calculations on the linked queries.

[mike02]

I like the idea of normalizing my data. So I have set up everything to do that now. All my issues in this post have been fixed, However Now i need to run a Dsum for my NetPositions instead of sum, as it needs to restart the calcs each time there is a new investment, and with respect to the clientID... This is what i currently Have.

NetPosition: (SELECT SUM(T2.TradePosition)     
FROM tblPositions AS T2     
WHERE T2.ClientID = T1.ClientID     


AND T2.MonthlyDate <= T1.MonthlyDate)

this is what i need:

NetPosition: (SELECT DSUM(T2.TradePosition, tblPositions, Needs to start for each InvestmentID, and ClientID, following MonthlyDate as order of sum)     
FROM tblPositions AS T2     
WHERE T2.ClientID = T1.ClientID     
AND T2.InvestmentID = T1.InvestmentID
AND T2.MonthlyDate <= T1.MonthlyDate)

I dont know how to write the criteria part..
the query is set up like the union table you made. What this calc does is sums the tradeposition, This is the BegPosition in that query...

Thanks in advance

[rpeare]

Can you provide an updated version of your database please