Help report generation & data grouping

Help report generation & data grouping

Post by star.. » Thu, 25 Apr 1996 04:00:00



Hi!

I'm trying to create a report which pulls data from 2 different
databases independently.  The problem I've encountered is that
FoxPro 2.6 won't allow two independent data groupings in the same
report, they have to be embedded.  The two databases do share one
field but they aren't always linked.  For example, one table
customer.dbf has a field cust_no and the other table order.dbf has
cust_no as well.  Say customer contains 3 records and 2 of the records
have matching cust_no to the order table.  What I want to see on the
report is:
custumer no
1            data from customer.dbf
2            .........
3            .........
customer no
1            data from order.dbf
3            .........

I tried to query the 2 databases but I lose records from customer
when there isn't a matching cust_no.
Any suggestions would be greatly appreciated!

Thanks,

 
 
 

Help report generation & data grouping

Post by Marc Grajowe » Fri, 26 Apr 1996 04:00:00



> Hi!

> I'm trying to create a report which pulls data from 2 different
> databases independently.  The problem I've encountered is that

<snip>

My approach to this would be to create a temporary file (like a cursor
from an SQL) before submitting to the report.  You could also add a
field that indicates from which database a record is originating.

Hope it helps,

Marc

 
 
 

Help report generation & data grouping

Post by M Darrin Chan » Tue, 30 Apr 1996 04:00:00



Quote:

>Hi!

>I'm trying to create a report which pulls data from 2 different
>databases independently.  The problem I've encountered is that
>FoxPro 2.6 won't allow two independent data groupings in the same
>report, they have to be embedded.  The two databases do share one
>field but they aren't always linked.  For example, one table
>customer.dbf has a field cust_no and the other table order.dbf has
>cust_no as well.  Say customer contains 3 records and 2 of the records
>have matching cust_no to the order table.  What I want to see on the
>report is:
>custumer no
>1            data from customer.dbf
>2            .........
>3            .........
>customer no
>1            data from order.dbf
>3            .........

>I tried to query the 2 databases but I lose records from customer
>when there isn't a matching cust_no.
>Any suggestions would be greatly appreciated!

You're trying to do an "outer join", which you can't directly do in
Fox.  There are ways, however.  Try this:

select customer.name, customer.cust_no, order.col1, order.col2
        from customer, order
        where customer.cust_no=order.cust_no
union
select customer.name, customer.cust_no, "" as col1, "" as col2
        from customer
        where not exists (select * from order where cust_no=customer.cust_no)
order by 1,2

There are caveats, as you may have guessed.  First, make sure the
query returning all the data is first.  Otherwise, you'll have hosed
fields.  This is particularly true with numeric fields (with text you
can use "                   " as a filler, 0.0000 doesn't work).  You'll
need to group by cust_no (or name) in the report, with the order columns
as the detail.  Then, be sure to select "remove line if blank" for the
detail.

Good luck.

        Darrin

--
Michael Darrin Chaney         FoxPro, Visual FoxPro, & Visual Basic Consulting

Phone: (812)333-6311 & (800)711-4240  Fax: (812)333-1894

 
 
 

Help report generation & data grouping

Post by Anderson Jef » Sat, 11 May 1996 04:00:00




> >Hi!

> >I'm trying to create a report which pulls data from 2 different
> >databases independently.  The problem I've encountered is that
> >FoxPro 2.6 won't allow two independent data groupings in the same
> >report, they have to be embedded.  The two databases do share one
> >field but they aren't always linked.  For example, one table
> >customer.dbf has a field cust_no and the other table order.dbf has
> >cust_no as well.  Say customer contains 3 records and 2 of the records
> >have matching cust_no to the order table.  What I want to see on the
> >report is:
> >custumer no
> >1            data from customer.dbf
> >2            .........
> >3            .........
> >customer no
> >1            data from order.dbf
> >3            .........

> >I tried to query the 2 databases but I lose records from customer
> >when there isn't a matching cust_no.
> >Any suggestions would be greatly appreciated!

> You're trying to do an "outer join", which you can't directly do in
> Fox.  There are ways, however.  Try this:

> select customer.name, customer.cust_no, order.col1, order.col2
>    from customer, order
>    where customer.cust_no=order.cust_no
> union
> select customer.name, customer.cust_no, "" as col1, "" as col2
>    from customer
>    where not exists (select * from order where cust_no=customer.cust_no)
> order by 1,2

> There are caveats, as you may have guessed.  First, make sure the
> query returning all the data is first.  Otherwise, you'll have hosed
> fields.  This is particularly true with numeric fields (with text you
> can use "                   " as a filler, 0.0000 doesn't work).  You'll
> need to group by cust_no (or name) in the report, with the order columns
> as the detail.  Then, be sure to select "remove line if blank" for the
> detail.

> Good luck.

>    Darrin

> --
> Michael Darrin Chaney         FoxPro, Visual FoxPro, & Visual Basic Consulting

> Phone: (812)333-6311 & (800)711-4240  Fax: (812)333-1894

Why not just copy the the databases to a temp dbf withxh contain all the fields in both databases?
                                                                Jnder-Man

 
 
 

Help report generation & data grouping

Post by Anderson Jef » Sat, 11 May 1996 04:00:00




> >Hi!

> >I'm trying to create a report which pulls data from 2 different
> >databases independently.  The problem I've encountered is that
> >FoxPro 2.6 won't allow two independent data groupings in the same
> >report, they have to be embedded.  The two databases do share one
> >field but they aren't always linked.  For example, one table
> >customer.dbf has a field cust_no and the other table order.dbf has
> >cust_no as well.  Say customer contains 3 records and 2 of the records
> >have matching cust_no to the order table.  What I want to see on the
> >report is:
> >custumer no
> >1            data from customer.dbf
> >2            .........
> >3            .........
> >customer no
> >1            data from order.dbf
> >3            .........

> >I tried to query the 2 databases but I lose records from customer
> >when there isn't a matching cust_no.
> >Any suggestions would be greatly appreciated!

> You're trying to do an "outer join", which you can't directly do in
> Fox.  There are ways, however.  Try this:

> select customer.name, customer.cust_no, order.col1, order.col2
>    from customer, order
>    where customer.cust_no=order.cust_no
> union
> select customer.name, customer.cust_no, "" as col1, "" as col2
>    from customer
>    where not exists (select * from order where cust_no=customer.cust_no)
> order by 1,2

> There are caveats, as you may have guessed.  First, make sure the
> query returning all the data is first.  Otherwise, you'll have hosed
> fields.  This is particularly true with numeric fields (with text you
> can use "                   " as a filler, 0.0000 doesn't work).  You'll
> need to group by cust_no (or name) in the report, with the order columns
> as the detail.  Then, be sure to select "remove line if blank" for the
> detail.

> Good luck.

>    Darrin

> --
> Michael Darrin Chaney         FoxPro, Visual FoxPro, & Visual Basic Consulting

> Phone: (812)333-6311 & (800)711-4240  Fax: (812)333-1894

Why not just copy the the databases to a temp dbf withxh contain all the fields in both databases?
                                                                Jnder-Man

 
 
 

1. help w/ data evironment & data reports

I need to create a report based on info in two separate databases.  Does
anyone know if this is possible using a Data Environment, and if so,
how?????

--
Nick Hasser

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

2. dbisorttable problem

3. Report Generation & Printing

4. Error 1069 when starting Windows 2000 Pro with SQL 7

5. Using the Shape command to multiple grouping of data for data reports

6. table update from flat file

7. URGENT HELP NEEDED - Report Generation

8. Temp tables?

9. HELP: Data Report & Images

10. Help for finding & reporting field data

11. Data generation: Test Data: Dummy Data