Diferent detail sections in the same report.

Hi, everybody!!!

I'm new user of VFP and I'm triying to figure out a
report that can display information from 3 different
tables, that is, a statement of incomes and expenses, as
follows:

-In the first table, I have all the codes for the people.
-In the second table, I have all the incomes for each
person of the first table.
-In the third table, I have all the expenses for each
person.

My report would be as follows:

Cod 1 - Mr. John Peterson

Incomes:
xxx - hhhhhhh - 1000
lll - fffffff - 2000
total 3000

Expenses
jjj - ooooooo - 500
ppp - rrrrrrr - 100
total 600


Net amount 2400


I would appreciate any help and support you can supply
me...!!!

B.R.

Rick
Tue Sep 16 13:39:50 CDT 2003

Luis,
The "easiest" way to do this is to create a cursor with an SQL Select =
that can then be used by the Report form. In this case, a Union of two =
selects that are ordered by the user, then income and expense types, =
would make the report form fairly simple with two groupings.

SELECT na.person, "1" as mytype, in.descript, in.amount, ;
from myNames na, myIncomes in ;
Where na.id =3D in.id ;
UNION
SELECT na.person, "2" as mytype, ex.descript, ex.amount ;
from myNames na, myExpenses ex ;
Where na.id =3D ex.id ;
ORDER by 1, 2 ;
INTO cursor MyReportData

Rick

"Luis C." <nemesis0506@hotmail.com> wrote in message =
news:037201c37c7e$41cb6c80$3101280a@phx.gbl...
> Hi, everybody!!!
>=20
> I'm new user of VFP and I'm triying to figure out a=20
> report that can display information from 3 different=20
> tables, that is, a statement of incomes and expenses, as=20
> follows:
>=20
> -In the first table, I have all the codes for the people.
> -In the second table, I have all the incomes for each=20
> person of the first table.
> -In the third table, I have all the expenses for each=20
> person.
>=20
> My report would be as follows:
>=20
> Cod 1 - Mr. John Peterson
>=20
> Incomes:
> xxx - hhhhhhh - 1000
> lll - fffffff - 2000
> total 3000
>=20
> Expenses
> jjj - ooooooo - 500
> ppp - rrrrrrr - 100
> total 600
>=20
>=20
> Net amount 2400
>=20
>=20
> I would appreciate any help and support you can supply=20
> me...!!!
>=20
> B.R.

John
Tue Sep 16 13:53:05 CDT 2003

Hey B.R.,

This could be handled in a few different ways. A common method would be to
query your three tables into a flat cursor (sorted by person and financial
type (income v. expense)) then report on the single file. Set up two
groupings, setting the person's info as the outer grouping and the financial
type as the inner type. Then display the financial details in the detail
band.

You might want to use a query similar to the following to tie them all
together.

SELECT Person.PersonID, Person.Name, Income.Code, Income.Amount, 1 AS
SortOrder ;
FROM Person JOIN Income ON Person.PersonID = Income.PersoID ;
INTO CURSOR curFinancials ;
UNION ;
SELECT Person.PersonID, Person.Name, Expense.Code, Expense.Amount, 2 AS
SortOrder ;
FROM Person JOIN Expense ON Person.PersonID = Expense.PersonID ;
ORDER BY 1, 5

HTH,

John

"Luis C." <nemesis0506@hotmail.com> wrote in message
news:037201c37c7e$41cb6c80$3101280a@phx.gbl...
> Hi, everybody!!!
>
> I'm new user of VFP and I'm triying to figure out a
> report that can display information from 3 different
> tables, that is, a statement of incomes and expenses, as
> follows:
>
> -In the first table, I have all the codes for the people.
> -In the second table, I have all the incomes for each
> person of the first table.
> -In the third table, I have all the expenses for each
> person.
>
> My report would be as follows:
>
> Cod 1 - Mr. John Peterson
>
> Incomes:
> xxx - hhhhhhh - 1000
> lll - fffffff - 2000
> total 3000
>
> Expenses
> jjj - ooooooo - 500
> ppp - rrrrrrr - 100
> total 600
>
>
> Net amount 2400
>
>
> I would appreciate any help and support you can supply
> me...!!!
>
> B.R.

Cindy
Thu Sep 18 10:20:24 CDT 2003

Hi Luis,

Here's a little more on the technique Rick and John are describing:
http://fox.wikis.com/wc.dll?Wiki~MultiChildFoxProReports. You might also
like to read Cathy Pountney's book "The Visual FoxPro Report Writer: Pushing
it to the Limit and Beyond" from
http://www.hentzenwerke.com/catalogpricelists/vfprw.htm.

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy.winegarden@mvps.org, www.cindywinegarden.com

"Luis C." <nemesis0506@hotmail.com> wrote in message
news:037201c37c7e$41cb6c80$3101280a@phx.gbl...

> I'm new user of VFP and I'm triying to figure out a
> report that can display information from 3 different
> tables, ....