Print Page | Close Window

what should i do?

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3150
Printed Date: 27 May 2024 at 5:47am


Topic: what should i do?
Posted By: kevincloud999
Subject: what should i do?
Date Posted: 09 May 2008 at 8:31am
hello i have a little prob.

i have 4 tables

1. invoice table (currenly invoice)
2. dyinvoice table (history invoice)
3. gl invoice (currenly the gl invoice)
4. dy gl invoice ( history gl invoice)

in the invoice table the first invoice number is 1245 and in the dyinvoice table the first invoice number is 1 and the last is 1244

and the gl invoice the first record would start at 1245 and the dy gl invoice the first record would be 1 and last would 1244

what i want to do in crystal reports is i want a field that would start from invoice 1 to the last invoice number and a field for the same with both the gl tables

kinda like merge the invoice tables into one field so is start from invoice 1 and to the last also for the gl tables


thank
kevin


Replies:
Posted By: Lugh
Date Posted: 12 May 2008 at 4:47am
To get all four tables in one report is going to be a bit tricky.  You are going to need to use a SQL Command object (instead of adding the tables to the report, you will use Add Command to add a query).

The query will look something like:

SELECT * FROM
    (SELECT * FROM invoice
     UNION ALL
     SELECT * FROM dyinvoice) inv_union
JOIN
    (SELECT * FROM gl_invoice
     UNION ALL
     SELECT * FROM dy_gl_invoice) gl_union
ON inv_union.inv_num = gl_union.gl_num



If you've never used UNION before, you need to know that the columns for both halves of the UNION need to line up.  E.g., you can't have an extra "date archived" column in the middle of the dyinvoice table.  For that reason, never actually use the "SELECT *" phrase.  By listing out the columns, it is much easier to make sure they line up.

Posted By: kevincloud999
Date Posted: 12 May 2008 at 6:18am
thank Lugh i am doing so now will let you know what is the out come

thank again

Posted By: kevincloud999
Date Posted: 12 May 2008 at 6:24am
oh lugh i forgot to tell you my data source is Visual Foxpro database connecting use ODBC 

Posted By: Lugh
Date Posted: 12 May 2008 at 6:42am
I don't think that should be an issue.  I have never used FoxPro, but it should support UNION queries.  It's ANSI standard and all.


Print Page | Close Window