Another Previous Record Query

[Dwight]

Hello,

Attached is a database with my problem. I have a table and two querys. Query1 does a simple sum and then Query2 should calculate a pct change. I need to compare the current value to the previous day's value and there's the rub.

BegValue will always equal the previous day’s EndValue.

This topic has been explained before and I have reviewed the discussions. But in my case there are multiple clients and multiple dates so I couldn't work it all out.

Why is this so easy in Excel but so hard in Access? I need to do this in a query not a report and I do not want to use the Dlookup function because I think it will get overwhelmed. I think the SQL Query Select...From....Where should work but I need some help.

I am new to databases but have learned a lot from this site.

Thanks, Dwight

 

[Vassago]

Uh, so where's the attached database?

 

[Dwight]

Hopefully it is attached this time.

 

[Dugantrain]

I have been going round and round with this problem. If someone has answered this question/knows the answer then, please advise.

 

[Jon K]

I have rebuilt two queries:

qryOne:-
SELECT [Account Value By Asset Class].*,
CCur(nz(Cash)+nz(Equity)+nz(Bond)) AS End_Account_Value,
(Select Count(*) from [Account Value By Asset Class] as s where s.Client_ID=[Account Value By Asset Class].Client_ID and s.Value_Date<=[Account Value By Asset Class].Value_Date) AS Curr, Curr+1 AS Prev
FROM [Account Value By Asset Class];

qryTwo:-
SELECT Curr.Client_ID, Curr.Value_Date, Curr.Cash, Curr.Equity,
Curr.Bond, Curr.End_Account_Value,
format(IIf(Prev.End_Account_Value=0, Null, Curr.End_Account_Value/Prev.End_Account_Value-1),"Percent") AS Pct_Change
FROM qryOne AS Prev RIGHT JOIN qryOne AS Curr ON (Prev.Prev = Curr.Curr) AND (Prev.Client_ID = Curr.Client_ID);


The Curr and Prev fields in qryOne are for the joining purpose in qryTwo, which joins qryOne to itself using a RIGHT JOIN.


Note: As a subquery (i.e. a Select query in brackets) is used in qryOne, running the queries will take time if the table contains many records.

The DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.

 

[Dwight]

Your solution is elegant and effective. My ego was soaring before I saw it because after weeks of struggle I figured out my own solution. I did not understand aliases but once I realized what they were I could do the self join with the counter trick.

I had to reduce the query to running on individual clients because my counter would not reset with each new client like yours does. Is this what the Select As s. part of your query does? Does it mean As "start?"

As you can see from the message board (I got this far..... )I now have another hurdle to cross. And once again I might be able to solve it after a few weeks. But any help before that would be much appreciated.

Vielen Dank,
Dwight

 

[Jon K]

> .... my counter would not reset with each new client like yours does. Is this what the Select As s. part of your query does? Does it mean As "start?" <


Yes, with each new client, the counter is reset by
where s.Client_ID = [Account Value By Asset Class].Client_ID
in the subquery.

The alias s has no special meaning. I just arbitrarily chose it to stand for subquery.