Report generation???

[Joesun]

hi,
i have three tables like bank_bal,daily_trans.
My company have four bank accounts. i did in my application, whenever payment & receipts are made that storing in daily_trans.dbf. eg:
date a/c pay receipt
01/01/2002 a/c1 50 100
01/01/2002 a/c1 75 50
02/01/2002 a/c2 25 30
02/01/2002 a/c2 10 20... so on

once administrator process these transactions, each day bank balance updating the bank_bal.dbf. as follows:-

eg: date a/c1_bal a/c2_bal a/c3_bal a/c4_bal
01/01/2002 25 200 300 400
02/01/2002 200 15 400 500

when i generate consolidate report,that link to bank_bal.date group by bank accounts, it is showing all bank accounts balances for the day.

Here i need only the grouped bank accounts balance of that day only

i tried a lot. But no output.

Any help in this regard would be highly appreciated.

Thanks in advance

Joesun

 

[ramani]

Hi,

1. The file structure you follow is not a good way to do that. Opinion could fdiffer. If I am asked to suggest ..
I will make the Bank_bal.dbf with structure..
1. date field
2. A/c field (may be code or a/c no.. it depends)
3. Amount field
This way, you can cast your reporte asily.
Another reason is, that what if the user opens the fifth a/c?. The above structure will work for any number of accounts and all the reports will function without change.

In the existing structure it is difficult when things are changed.

2. However.. for the question that you have specificaly put..

You cannot link by one account number..as you said.. one SQL select..
However we can do it by introducing a intermediary cursor.

** Create a temporary cursor to hold the data
CREATE CURSOR tBal (dDate D, cAccount C(10), nAmount Y)

** Create variable for 4 bank accounts
DECLARE aAccount(4)
aAccount(1) = "myAccountCode1"
aAccount(2) = "myAccountCode2"
aAccount(3) = "myAccountCode3"
aAccount(4) = "myAccountCode4"

SELECT bankBal
SCAN
** collect record data in an array
SCATTER TO aField

** scan the array to copy to cursor
** first array column = date
** 2nd,3rd.. are 1stA/c, 2ndA/c etc.
FOR i = 1 to 4 && 4 banks
** If balance is zero.. loop
IF aField(i+1) = 0
LOOP
ENDIF
** create one record in the cursor
INSERT INTO tBal (dFate, cAccount, nAmount) ;
VALUES aField(1), aAccount(i), aField(i+1))
ENDFOR
ENDSCAN
**

Now the cursor tBal holds the data in a single account basis. You can index this if you want and use this cursor table to produce your report .. filter the table based on account number.. etc..

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com