Select from where max(date) valuation in EUR 2

[straatlang]

I hope somebody can help me with the next problem.
There are three tables involved:

Table: movements.

Value Date Account Amount Cur DRCR
1-1-2003 123 2000 EUR CR
1-2-2003 456 1500 EUR CR
1-2-2003 123 1450 USD CR
1-4-2003 456 1250 EUR CR
1-7-2003 123 800 USD CR
15-1-2003 123 950 EUR CR
20-1-2003 789 5000 USD CR
27-1-2003 456 5000 USD CR
28-1-2003 456 3000 USD CR
29-1-2003 123 10000 EUR DT


Table :names

Account Name
123 ABC
456 DEF
789 GHJ

Table :rates

CurID Erate RateDate
EUR 1 01-01-2003
USD 0.98 01-01-2003
GBP 0.65 02-01-2003
USD 0.95 02-01-2003
GBP 0.66 03-01-2003
USD 0.90 03-01-2003
GBP 0.34 03-01-2003
ect

The query should show the balances per account per currency per <=Valuedate. And the balance as
EUR but also the currency rate that is <= to the value date that I enter.

The query that I am using (thanks to Swampboogie) is almost correct but is not showing the correct
exchange rate.

Who can help me out.

The Query as it is now:

SELECT M.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, M.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, R.Erate, N.Name, Erate*Balance AS V_EURO
FROM (Movements AS M INNER JOIN [Names] AS N ON M.Account=N.Account) INNER JOIN Rates AS R ON M.Cur=R.CurID
WHERE M.[Value Date] <= DateValue([Enter Value Date]) and R.rateDate = (select max(rateDate) from rates where curID = R.curID and rateDate <= M.[Value Date] )
GROUP BY M.Account, M.Cur, R.Erate, N.Name;

When I enter 31-1-2003 as the value date the result is then

Account Balance CurFLAG SelectedDate Erate Name V_EURO

123 7050 EUR DR 31-01-2003 1 ABC 7050
123 2250 USD CR 31-01-2003 0.98 ABC 2205
456 2750 EUR CR 31-01-2003 1 DEF 2750
456 8000 USD CR 31-01-2003 0.9 DEF 7200
789 5000 USD CR 31-01-2003 0.95 GHJ 4750


The erate is not &quot;correct&quot; what i want to see is the same rate in currencies
for all account namely the one <= to the value date.

EXpected result:

Account Balance CurFLAG SelectedDate Erate Name V_EURO
123 7050 EUR DR 31-01-2003 1 ABC 7050
123 2250 USD CR 31-01-2003 0.90 ABC 2025
456 2750 EUR CR 31-01-2003 1 DEF 2750
456 8000 USD CR 31-01-2003 0.90 DEF 7200
789 5000 USD CR 31-01-2003 0.90 GHJ 4275


I dont how to do this who can help me out.

Regards

Straatlang

 

[MichaelRed]

Ms. Help clearly states that -within Jet SQL- all dates MUST be expressed / entered in &quot;U.S. Standard&quot; format (e.g. mm/dd/yy) you can also use simplistic variations thereof (&quot;m&quot; ve &quot;mm&quot;, &quot;yyyy&quot; vs &quot;yy&quot but the date element order is not amenable to other formats for any selection or ordering or grouping operations. You can learn the details through Help, and (perhaps) even see some useful examples.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[straatlang]

Update

I have changed the query into:

SELECT M.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, M.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, R.Erate, N.Name, Erate*Balance AS V_EURO
FROM (Movements AS M INNER JOIN [Names] AS N ON M.Account=N.Account) INNER JOIN Rates AS R ON M.Cur=R.CurID
WHERE (((M.[Value Date])<=DateValue([Enter Value Date])) AND ((R.RateDate)=(select max(rateDate) from rates where curID = R.curID and rateDate <=DateValue )))
GROUP BY M.Account, M.Cur, R.Erate, N.Name;

The result is correct but I dont know if this the correct way to solve this problem.

Now I have to enter the Value date twice. Is there a way to enter the date only ones and still get the same result ?

 

[r937]

> Now I have to enter the Value date twice.
> Is there a way to enter the date only ones
> and still get the same result ?

yes, don't use two different prompts

you have [Value Date] and [Enter Value Date]


rudy

 

[straatlang]

Hi Rudy,

Do you mean that I have to remove one prompt or that the two prompts should be the same. I have tried this:

SELECT M.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, M.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, R.Erate, N.Name, Erate*Balance AS V_EURO
FROM (Movements AS M INNER JOIN [Names] AS N ON M.Account=N.Account) INNER JOIN Rates AS R ON M.Cur=R.CurID
WHERE (((M.[Enter Value Date])<=DateValue([Enter Value Date])) AND ((R.RateDate)=(select max(rateDate) from rates where curID = R.curID and rateDate <=DateValue )))
GROUP BY M.Account, M.Cur, R.Erate, N.Name;

Then I have to enter 3 date ?

SELECT M.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, M.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Value Date] AS SelectedDate, R.Erate, N.Name, Erate*Balance AS V_EURO
FROM (Movements AS M INNER JOIN [Names] AS N ON M.Account=N.Account) INNER JOIN Rates AS R ON M.Cur=R.CurID
WHERE (((M.[Value Date])<=DateValue) AND ((R.RateDate)=(select max(rateDate) from rates where curID = R.curID and rateDate <=DateValue )))
GROUP BY M.Account, M.Cur, R.Erate, N.Name;

Still no result ?

Can you help me out.

Regards

Straatlang

 

[dodge20]

After you enter the first value date you are creating an alias called SelectedDate. You should be able to put this in wherever you have value date there after.

Dodge20

 

[straatlang]

Thank you very much it works

SELECT M.Account, Abs(Sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, M.Cur, IIf(Sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, SelectedDate, R.Erate, N.Name, Erate*Balance AS V_EURO
FROM (Movements AS M INNER JOIN [Names] AS N ON M.Account=N.Account) INNER JOIN Rates AS R ON M.Cur=R.CurID
WHERE (((M.[Value Date])<=SelectedDate) AND ((R.RateDate)=(select max(rateDate) from rates where curID = R.curID and rateDate <=SelectedDate)))
GROUP BY M.Account, M.Cur, R.Erate, N.Name;