multiple table on single report

[rommeltm00]

i have five tables to be printed in report, the scenario is this

print the all record in the first table
then total

print the all record in the second table
then total

print the all record in the third table
then total

print the all record in the fourth table
then total

print the all record in the fifth table
then total

then grand total

 

[Mike Lewis]

The easiest way is to create a single cursor, containing data from all five tables, plus a new field so that you can group the report according to the table.

To create the cursor, do something like this:

SELECT "1" AS GroupField, * FROM Table1;
UNION ;
SELECT "2" As GroupField, * FROM Table2 ;
UNION ;
<repeat for remaining tables>
INTO CURSOR AllTables

SELECT AllTables
REPORT FORM ....

Within the report, group on GroupField, and insert subtotals and grand totals in the group footer and summary bands.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[rommeltm00]

m.cprojectno=cprojectno
SELECT a.cmodule,a.celevation,a.ndepthcar,a.nheightcar,a.nwidthcar,a.ncarcassq,
(b.materialsa*b.materialsm)+(b.machininga*b.machiningm)+
(b.laboura*b.labourm)+(b.packdelia*b.packdelim) as total1,a.mnotes;
FROM customer a,alltotal b;
WHERE a.ccode = b.ccode AND a.cprojectno = m.cprojectno AND a.lfinal;
ORDER BY celevation;
into TABLE c:\xdbf

SELECT * FROM custoded WHERE cprojectno = m.cprojectno INTO TABLE c:\xdbf0

this are the two tables that i want to print on a report
first print all records in "xdbf"

then print all records in "xdbf0"

 

[Mike Lewis]

So, what exactly is your question?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[MarciaAkins]

i have five tables to be printed in report, the scenario is this

If you are using VFP 9, I believe that you can do this quite easily using multiple detail bands.

Marcia G. Akins

http://www.TightlineComputers.com

 

[rommeltm00]

theres an error selects are not union compatible. number of fields do not match.

there structure is not the same at all.

multiple detail band prints earch record

record 1 on dbf then record 1 on dbf2
record 2 on dbf then record 2 on dbf2 and so on

all i want to do is to print all records in dbf1 and then all records in dbf2

please help im stuck

thank you in advance.

 

[Mike Lewis]

all i want to do is to print all records in dbf1 and then all records in dbf2

Excuse me, but that's not what you said in your original question. You said you had five tables, now you say you have two. More importantly, you originally said you wanted grand totals for the five tables.

It was because of the requirement for grand totals that I suggested a union. Without that requirement, there's no point in trying to combine the tables into a single cursor. You might as well run the report twice (five times?), once from each table. If you don't want them to appear as separate print jobs, use NOPAGEEJECT in each report except the last.

You also said:

theres an error selects are not union compatible. number of fields do not match.

Well, you know, you'll get much better answers if you provide this sort of information at the outset. All you said was that you wanted five tables to be printed. If you had told us about the structures of the tables, we could have suggested a SELECT statement that would have produced a suitable output.

I'm sorry if it sounds like I'm ranting, but I'm sure your requirement is straightforward, and you will get a useful solution, but only if you give us sufficient information abuot the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[rommeltm00]

im sorry for the confusion what i mean on that 5 or 2 table is that they have different structure, as of now i have made a solution.

m.cprojectno=cprojectno
SELECT "1" AS GroupField,a.cmodule+SPACE(15) as cmodule,a.celevation,a.ndepthcar,a.nheightcar,a.nwidthcar,a.ncarcassq,(b.materialsa*b.materialsm)+(b.machininga*b.machiningm)+(b.laboura*b.labourm)+(b.packdelia*b.packdelim) as total1;
FROM customer a,alltotal b;
WHERE a.ccode = b.ccode AND a.cprojectno = m.cprojectno AND a.lfinal;
into TABLE c:\dbf

SELECT "2" AS GroupField,'OTHER CHARGES' as celevation,cnotes as cmodule,namount as total1,nquantity as ncarcassq FROM custoded WHERE cprojectno = m.cprojectno INTO TABLE c:\dbf0

use dbf
appe from dbf0
inde on <fieldname> to ttx

i made "AS " statement so the field will be the same and i append it and index it

thank you for your response as i get all your ideas so i arrived on this solution, thank you, and more power, God Bless