Select From ? Account Balance 2

[straatlang]

I Want to make a query and the result should calculate the total balance
per Accountnumber per Value date by entering a Value date.

Table: Movements

Value Date Accountnumber Amount Cur DR/CR
01-1-2003 123 2000 EUR CR
01-2-2003 456 1500 EUR CR
01-2-2003 123 1450 USD CR
01-4-2003 456 1250 EUR CR
01-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 789 1500 USD CR
30-1-2003 123 1000 EUR DR


The expected result per value date 15-1-2003

Account Balance Currency DR/CR
123 2950 EUR CR
123 2250 USD CR
456 2750 EUR CR

The expected result per value date 31-1-2003

Account Balance Currency DR/CR
123 1950 EUR CR
123 2250 EUR CR
456 8000 USD CR
789 6500 USD CR

 

[scriverb]

Here is the SQL for your query:
Select A.Account, Sum(Amount) as Balance, A.Cur, A.DR/CR
From Movements as A
Where A.[Value Date] = DateValue([Enter Value Date]);

The only problem here is working with your date format of dd-mm-yyyy. This is a little difficult at times. Your International settings say one thing but ACCESS doesn't always recognize it as such. We may have to work with this a little bit to get the select criteria correct.

See how this works and get back with me.

Bob Scriver

 

[r937]

i think you'll want <= rather than = for the date test

also, a GROUP BY

Select A.Account, Sum(Amount) as Balance, A.Cur, A.DR/CR
From Movements as A
Where A.[Value Date] <= DateValue([Enter Value Date])
GROUP BY A.Account, A.Cur, A.DR/CR


rudy

 

[straatlang]

Dear ScriverB,

I have tried your Query. Access gives an error message.

&quot;You try to execute a Query where the expression Account is no part of an statistical function&quot;

Sorry for the translation but it was in Dutch and I had to translate.

If it is helpfull I can change the date format into mm/dd/yyyy.

Thanks in advance

Regards
Straatlang

 

[straatlang]

Dear Rudy,

You were correct I needed <=.

Do you also have an Idea when I want to see the total balance in EUR on a particular Valuedate.

I want to enter the currency and value date

The result should be:

Balance in XXX on Valuedate

Account Balance
123 XXXXX
456 XXXXX
789 XXXXX



Regards

Straatlang

 

[straatlang]

Sorry there is still one other problem with this Query

The result is that there are now two balance for account 123 one DR and one CR balance
This the result.

Account Balance Cur DRCR
123 2950 EUR CR
123 1000 EUR DT
123 2250 USD CR
456 2750 EUR CR
456 8000 USD CR
789 5000 USD CR

the balance of account 123 should be 1950 EUR CR

the query that I am using is (thanks to Rudy)

SELECT A.Account, Sum(Amount) AS Balance, A.Cur, A.DRCR
FROM Movements AS A
WHERE A.[Value Date]<=DateValue([Enter Value Date])
GROUP BY A.Account, A.Cur, A.DRCR;

Regards

Straatlang

 

[r937]

yeah, i should've seen the DRCR and realized it's a plus/minus situation

try this --

SELECT A.Account, A.Cur
, Sum( Amount * IIF(CRDR='CR',1,-1) ) as Balance
FROM Movements AS A
WHERE A.[Value Date]<=DateValue([Enter Value Date])
GROUP BY A.Account, A.Cur


this will give you the right total, assuming you put the -1 with the right value

the SUM(...) will then be plus or minus, in which case you might want to generate an extra column with 'CR' or 'DB' depending on the sum, just let me know


rudy

 

[scriverb]

Rudy: Thanks for picking up on the Group By. I left it out on the initial pass by mistake. Stepped away for a while but see that Straatlang was in good hands.

Good job.

Bob Scriver

 

[straatlang]

Thanks you very much rudy.

SELECT A.Account, A.Cur, Sum(Amount*IIf(DRCR='CR',1,-1)) AS Balance
FROM Movements AS A
WHERE A.[Value Date]<=DateValue([Enter Value Date])
GROUP BY A.Account, A.Cur;

But what if I want to see the balance in XXX currency per <= Valuedate Per Account.

Then I want have anymore question


I want to enter the currency and value date

The result should be:

Balance in XXX on Valuedate

Account Balance
123 XXXXX
456 XXXXX
789 XXXXX



Regards

Straatlang

 

[scriverb]

I think this would do it:
SELECT A.Account, Sum(Amount*IIf(DRCR='CR',1,-1)) AS Balance
FROM Movements AS A
WHERE A.[Value Date]<=DateValue([Enter Value Date]) and A.Cur = [Enter Currency Type]
GROUP BY A.Account;

Just another variation.


Bob Scriver

 

[r937]

select Account
, abs( sum( Amount * iif(CRDR='CR',1,-1) ) ) as Balance
, iif( sum( Amount * iif(CRDR='CR',1,-1) ) > 0
, 'CR' , 'DR' ) as CRDR
from Movements
where [Value Date] <= DateValue([Enter Value Date])
and Cur = [Enter Cur]
group
by Account

this allows you to enter the currency you want

formatting it on a heading line is your problem

notice the sum is shown as an absolute value, and whether it is plus or minus is reflected in the generated CRDR column

rudy

 

[straatlang]

This works but what if I also need to see the DR or CR flag and the value date and Currency in the report

Account Balance CUR DRCR Per Valudate
123 1950 EUR CR 31-01-2003
456 2750 EUR CR 31-01-2003

Any suggestions.

 

[r937]

just add them to the SELECT list

select Account
, abs( sum( Amount * iif(CRDR='CR',1,-1) ) ) as Balance
, iif( sum( Amount * iif(CRDR='CR',1,-1) ) > 0
, 'CR' , 'DR' ) as CRDR
, [Enter Cur] as Cur
, [Enter Value Date] as SelectedDate
from Movements
where [Value Date] <= DateValue([Enter Value Date])
and Cur = [Enter Cur]
group
by Account

 

[straatlang]

You are great just one small adjustment.

SELECT Account, abs(sum(Amount*IIf(DRCR='CR',1,-1))) AS Balance, IIf(sum(Amount*IIf(DRCR='CR',1,-1))>0,'CR','DR') AS FLAG, [Enter Cur] AS Cur, [Enter Value Date] AS SelectedDate
FROM Movements
WHERE [Value Date]<=DateValue([Enter Value Date]) And Cur=[Enter Cur]
GROUP BY Account;

Thank you so much.

Regards

Straatlang