cancel
Showing results for 
Search instead for 
Did you mean: 

Working with multiple tables

Former Member
0 Kudos

<p>Hello, I&#39;m a forum member that has 2 years of crystal experience (OJT). I have an issue which I&#39;m sure is quite simple but I can&#39;t seem to solve.</p><p> I have 3 tables in one database:</p><p>1. ORDER_MASTER with fields of BRANCH, ORDER and SERIAL_NO</p><p>2. PRODUCT_ORDER with fields of SERIAL_NO, STATUS and DESCRIPTION</p><p>3. ADDON_ORDER with fields of SERIAL_NO, STATUS and DESCRIPTION</p><p>I link 2 and 3 to table 1 by SERIAL_NO. Fields STATUS and DESCRIPTION have similar values and syntax. I&#39;d like to be able to extract STATUS and DESCRIPTION data for both tables with use of a single field.</p><p>In simpler terms, PRODUCT_ORDER may have 3 records with SERIAL_NO 1 and ADDON_ORDER may have 2 records with SERIAL_NO 1 and I&#39;d jsut like to be able to get a simple list of all 5 records each on their own line.</p><p> Hope this makes sense and thanks for your help.</p><p>Carl</p>

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Carl,

You can probably do 2 different details sections and place both status and description fields from the different tables into the different sections and then do some supression of certain fields//sections based on calculations or duplicates.

I would suggest using a command object to do a union query and put these tables together. That is, if the report is as simple as this, there may be complexities that make this not possible.

Simply put if you are only using these 3 tables in the way you've indicated above.
Try this.

Create a command object and paste this query in (change table/field names as needed)
SELECT ORDER_MASTER.SERIAL_NO, ADDON_ORDER.Status, ADDON_ORDER.Description
FROM ORDER_MASTER INNER JOIN ADDON_ORDER ON ORDER_MASTER.SERIAL_NO = ADDON_ORDER.SERIAL_NO;
UNION
SELECT ORDER_MASTER.SERIAL_NO, PRODUCT_ORDER.Status, PRODUCT_ORDER.Description
FROM ORDER_MASTER INNER JOIN PRODUCT_ORDER ON ORDER_MASTER.SERIAL_NO = PRODUCT_ORDER.SERIAL_NO
You'll be left with one table called command added to your report.

Let me know how it works out for you
Todd

Former Member
0 Kudos

Thanks for your help. I need to work on unions. Below is the object I tried to create:

SELECT PU_ORDER_MASTER.SERIAL_ID, PU_ORDER_DETAIL_ADDON.Status, PU_ORDER_DETAIL_ADDON.Description,
PU_ORDER_DETAIL_ADDON.Vendor
FROM PU_ORDER_MASTER INNER JOIN PU_ORDER_DETAIL_ADDON ON PU_ORDER_MASTER.SERIAL_ID = PU_ORDER_DETAIL_ADDON.MASTER_SERIAL_ID;
UNION
SELECT PU_ORDER_MASTER.SERIAL_ID, PU_ORDER_DETAIL_PRODUCT.Status, PU_ORDER_DETAIL_PRODUCT.Description,
PU_ORDER_DETAIL_PRODUCT.Vendor
FROM PU_ORDER_MASTER INNER JOIN PU_ORDER_DETAIL_PRODUCT ON PU_ORDER_MASTER.SERIAL_ID = PU_ORDER_DETAIL_PRODUCT.MASTER_SERIAL_ID

The table are now the actual names and I'm trying to join 3 records instead of 2. When I do the multiple detail lines I get duplicated info. If I have 2 records with the serial_id in addon and 2 records with the serial_id in product, I get:

Product 1 and Addon 1

Product 1 and Addon 2

Product 2 and Addon 1

Product 2 and Addon 2

This is so frustrating because I know there is a logical formula for getting the information.Â

Former Member
0 Kudos

<p>If you cant get your command object to work and you want to work with the details sections, try using a combination of suppressing duplicates on the field objects and suppressing blank sections on the detail sections.</p><p>If you can supply more infomation about why your command object might not be working i might be able to help more.</p><p>Todd <br /></p>

Former Member
0 Kudos

Hi,

This is simple but some what typical.

 Pls check like this do join with 1 >2 and 1-> 3, and create a formula do display the status and description.

 then u will get all 5 in one line....Â

Thanks

Rajesh