Print Page | Close Window

linking tables removes data

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7115
Printed Date: 05 May 2024 at 9:50pm


Topic: linking tables removes data
Posted By: Jimminy-Bob
Subject: linking tables removes data
Date Posted: 22 Jul 2009 at 12:39am
I have a number of tables (i think 3 in total) with vaious data in them, mainly to do with attendance. I have them all linked up joined on an ID field which is unique to each person) so that i can gather data from all 3 tables.

The problem is when i try to use a formula with values from one of the tables. Some of the IDs are not in this table (as this one is the persons attendance at a different site and so some have not gone there) and so the moment i put this formula into the report the IDs of the people that are not in the table disappear and i cant see why.

I've tried changing the report links between the tables and so on but nothing i've done works. Can anyone suggest something i can try?

Oh and i've found that i cant put all the data into a single table as when i use the formula it brings back the wrong value. I'm guessing its something to do with the links or something but i just cant think what it could be
 
Thanks for your help

Any help is appreciated


Replies:
Posted By: DBlank
Date Posted: 22 Jul 2009 at 7:03am

I assume youa re using a left join to make sure you get all the records.

If you use anykind of select statement on a field that come fom either table that has the fewer records it will change this to an inner join and the records will drop.
If you are just palcing a formula field on the canvas try altering to handle the NULL ID values to include them in the process.
If isnull(table.ID) then " " else formula here
Make sure you use the ID field from the table that has the fewer records.

Posted By: Jimminy-Bob
Date Posted: 24 Jul 2009 at 5:47am
Thanks this works perfectly! I've been stuck on this for ages and not even thought of that


Print Page | Close Window