×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

TRIAL BALANCE GROUPING

TRIAL BALANCE GROUPING

TRIAL BALANCE GROUPING

(OP)
hello everyone!
im not quite adept with ms visual foxpro... but i am working in a company where i have adopted a system from previous programmer... i learned to appreciate the language (VFP 9.0) and so i tried to develop an accounting system that would
allow the automation in all the transaction of accounting entries. My problem is i am having difficulties in grouping
data of the same account name and sum its total in detail band report.


as you can see, data must be group according to accountname and its amount must be sum up or calculated.

any idea? thanks for the help in advance.

RE: TRIAL BALANCE GROUPING

Welcomem to the forum.

There are two approaches you can take to solve this problem.

The first option is to do the grouping and totalling in advance, before you do the report. You get the data into a cursor, and use that cursor to drive the report.

For example, something like this:

SELECT AccountName, SUM(Amount) AS Amount FROM Transactions GROUP BY AccountName ;
INTO CURSOR csrTrans


Then use csrTrans to drive the report. The report would need no summary or calculations or grouping, as that has all been done in advance. Just place a record from csrTrans into the details band, with two columns: AccountName and Amount. You will also presumably need a grand total on Amount.

The second option would be to set up a goup within the report, and to use a report variable to sum the transactions. But I would prefer the first option, as it lets VFP do the grouping, etc. in the place where it does it best, that is, in a SQL statement. This approach also makes debugging easier.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: TRIAL BALANCE GROUPING

(OP)
just got back from a day trip (out of town)... sir mike, your idea was quite fine...
as it resolve issues of my concern except that it does not include the last column PK
which would determine the classification of accounting entries as debit and credit...
i added the entry pk to your suggestion:

SET safety OFF
SELECT AccountNam, SUM(Amountsum) AS Amountsum, pk;
FROM jev2017 GROUP BY AccountNam, pk;
INTO TABLE jevrep
report form trialbalance2.frx noconsole to printer prompt preview

i didnt used cursor in driving the report, instead i tried to put the selections to a temporary table since i am used to it...
anyways, i have one remaining issue to deal. i cant seem to get the grand total of a column based
on the debit and credit. i wish that i could do it before issuing the report and in an SQL statement... Thanks sir Mike for the reply.
Greatly appreciate your support...2thumbsup

RE: TRIAL BALANCE GROUPING

If I don't get it wrong you would need to multiply all pk="DR" with -1, so your SUM would change to

SUM(IIF(oj="CR",AmountSum,-Amountsum)) as Totalsum

You'll need to NOT group by Pk for that to work out and if you want all sums for CR, DR and both, you'll need to union:

CODE

SELECT AccountNam, SUM(Amountsum) AS Total, pk, .T. as partialsum;
FROM jev2017 GROUP BY AccountNam, pk;
UNION;
SELECT AccountNam, SUM(IIF(pk="CR",AmountSum,-Amountsum)) as Total, "  " as pk,  .F. as partialsum;
FROM jev2017 GROUP BY AccountNam;
INTO TABLE jevrep 

To cover the unusual case pk is neither CR nor DR you would perhaps apply ICASE:

CODE

SUM(ICASE(pk="CR",AmountSum,pk="DR",-Amountsum,0)) as Total 

Last, not least you might want to turn a positive total in the rows with empty pk (" " as pk) to CR while you would denote a negative total as DR and take the absolute amount. That's perhaps best done as UPDATE after creating the jevrep table:

CODE

UPDATE jevrep SET pk=IIF(Total<0,"DR","CR"), Total = Abs(Total) WHERE not partialsum and empty(pk) 
This has one problem, if you'd not always have records in the order first CR, then DR then total, though it's likely the case, I added the partialsum column for that matter to identify the overall total as not partialsum.

Bye, Olaf.

RE: TRIAL BALANCE GROUPING

Olaf has given you a good answer regarding totalling of debits and credits: multiply the debits by -1.

To display the symbol CR and/or DB in your report, you don't need to do anything in the cursor. Just format the field in the report. Do this:

- In the report designer, right-click on the Amount field and choose Properties.

- In the resulting dialogue, go to the Format tab.

- Select Numeric, then tick either "CR if positive" or "DB if negative" or both, depending on your requirements.

Regarding the grand total, you can't easily do that at the SQL stage. Better to do it within the report. Do this:

- In the report designer, select Optional Bands from the Report menu.

- Tick "Report has summary band".

- Place a new field in the Summary band.

- Open its Properties dialogue (see above); on the Calculate page, select Sum as the Calculation Type, and Report for "Reset baseed on".

Final point: I appreciate that you are using a temporary table rather than a cursor because that is what you are used to. However, I suggest you familiarise yourself with cursors, as they have several advantages over a temporary table:

- You don't have to worry about clashes with existing file names;

- You don't have to worry about multiple users trying to create tables with the same name;

- You don't have to clean up afterwards (cursors are automatically deleted when you have finished with them).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: TRIAL BALANCE GROUPING

(OP)
Olap,

sir, your suggestion is an option to take. Actually, i have added the functionality as options for checking as it actually yields accounts with variance or unbalance based on the results of trial balance. It makes life more easier now as tracing could easily be achieved by comparing DR/CR values. A million thanks to you sir.

Sir mike, i take your point of concern regarding the use of cursors rather than temp tables... Actually, i dont really have that idea as to the advantages of using cursors in making SQL statements... Thank you sir for keeping my job easier with your well timed advice. God Bless to both of you!

RE: TRIAL BALANCE GROUPING

The only reason to use a temporary table rather than a cursor is if you need it to exist after the code completes, for example, to be seen by another user. Otherwise, cursors are always a better choice.

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close