Print Page | Close Window

Multiple tables

Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6665
Printed Date: 03 May 2024 at 9:05am


Topic: Multiple tables
Posted By: johnl
Subject: Multiple tables
Date Posted: 11 Jun 2009 at 2:12am

Hi all

 

I’m losing my mind trying to solve this crystal reports.net with vb2008 problem, and wondered if you can help – your book doesn’t seem to cover this.

 

I have 4 tables

 

Lots, materials, pack rules, and QC history. The lot has 1 material code and many lot details. The lot also has 0 or multiple pack rules, and 0 or multiple QC history entries.

 

I am trying to lay out the form so that each lot only appears once and shows the pack rules(all for the lot) and QC history(all for the lot), as below:

 

 

Lot Code              Material Code      Status                Initial Quantity                 Quantity Remaining         

Lot001                   MatCode001         Passed               1500.00                                 1300.00                               
 

 

Pack Rule ID       Pack Size

1                        15

2                        25

3                        20

 

QC Amendment Date                    Amended By

20/10/2009                                         John L                  

21/10/2009                                         John L

25/10/2009                                         John L

 

 

I can’t use an SQL command because I need to loop through all the header fields and sub report header fields and change them to the required language (French, Spanish, Italian, German)

 

I can produce the required result only if I display the lot details with either the pack rules or QC history, and then add a sub report for the missing data, but I want all the details together on the report.

 

Please help me!!



-------------
johnl


Replies:
Posted By: lockwelle
Date Posted: 22 Jun 2009 at 2:16pm
do an outer join between the tables, so that only the records that match are displayed and not excluded.
 
In database expert, get to the links, and when you right click on them, you have the ability to select a right, left, or full outer join.  You will have to play around with them as 1) I don't use them 2) I don't which table is the 'right' table from the left.
 
In SQL it would be select * from lot left join pack on .... left join qc on ....
 
HTH


Print Page | Close Window