Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Gerzon
  Where is Gerzon?
 Yogyakarta
 Indonesia
 Gerzon
 To: Noker Lodia
  Where is Noker Lodia?
 irvine
 California - United States
 Noker Lodia
 Tags
Subject: RE: Query Result Like Bank Statement
Thread ID: 112291 Message ID: 112320 # Views: 2 # Ratings: 0
Version: Visual FoxPro 8 Category: General VFP Topics
Date: Tuesday, November 07, 2006 9:46:16 AM         
   


> > > > Hi experts,
> > > > Can someone give me SQL Statement that can generate result Like Bank Statement, I mean for Balance every row got from pervious row balance(debit-credit).
> > > > thanks
> > > >
> > > > Regards
> > > > Gerzon

> > >
> > >
> > >
> > > how about
> > >
> > >
SELECT debit,credit,(credit - debit) as balance from MyBankTable order by datestamp into cursor DRCRBAL

> >
> > For example I have Jurnal Table :
> > Journal_no Journal_Date
> > 1 04/11/2006
> > 2 04/11/2006
> > 3 05/11/2006
> >
> > and Journal_Detal Table :
> > Journal_no Acct_id Debet Kredit
> > 1 110 100.000 0
> > 1 120 0 100.000
> > 2 110 0 25.000
> > 2 410 5.000 0
> > 2 460 20.000 0
> > 3 110 0 50.000
> > 3 410 50.000 0
> >
> > also Account Table :
> > Acc_id Acc_Name
> > 110 Petty Cash
> > 210 Receivables Acct
> > 410 Office Expenses
> > 460 Rent Espenses
> >
> > I need SQL Result Like This :
> > Journal_No Journal_Date Acct_id Deposit Withdrawal Balance
> > 1 04/11/2006 120 100.000 0 100.000
> > 2 04/11/2006 410 0 5.000 75.000
> > 2 04/11/2006 460 0 20.000 55.000
> > 3 04/11/2006 410 0 50.000 5.000
> >
> > thanks Mr. Bernard but I hope you give me more, hope my detail Clearly enough.
> > really want help, it's posible to get query result just using 1 statement.?
> >
> > regards
> > Gerzon

>
> Try this:
> SELECT JOURNAL.JOURNAL_NO, JOURNAL.JOURNAL_DATE, JDETAIL.ACCT_ID, JETAIL.KREDIT AS DEPOSIT, JDETAIL.BEDET AS WITHDRAWAL
> FROM JOURNAL JOURNAL
> INNER JOIN JOURNAL_DETAIL JDETAIL
> ON JOURNAL.JOURNAL_NO = JDETAIL.JOURNAL_NO
> ORDER BY JOURNAL_DATE, ACCT_ID
>
> you can add a where clause for specific acct_id or date...
>
> Good luck.
>
> Noker Lodia
> The interesting thing about life is that we all have our own contradictions.


thanks noker,
but where can I get Balance that can be deducting from the above row and at end of row is the actual balance that we got. many thanks again.

regards
gerzon

ENTIRE THREAD

Query Result Like Bank Statement Posted by Gerzon @ 11/7/2006 5:00:52 AM
RE: Query Result Like Bank Statement Posted by Bernard Bout @ 11/7/2006 7:30:10 AM
RE: Query Result Like Bank Statement Posted by Gerzon @ 11/7/2006 8:51:55 AM
RE: Query Result Like Bank Statement Posted by Noker Lodia @ 11/7/2006 9:27:41 AM
RE: Query Result Like Bank Statement Posted by Gerzon @ 11/7/2006 9:46:16 AM
RE: Query Result Like Bank Statement Posted by tushar @ 11/7/2006 9:16:14 AM
RE: Query Result Like Bank Statement Posted by Gerzon @ 11/7/2006 9:39:03 AM
RE: Query Result Like Bank Statement Posted by tushar @ 11/7/2006 9:43:28 AM