Is there a solution to this problem?
In my work database contains probably hundreds of tables but to make it easier to understand, suppose there are just 2 tables for now and the CONTACT table below stores multiple records of an individual's job title. In real life, a person can work at more than one organisation. The problem is when I try to include the jobtitle field, it will pull all their record. This is indicated and controlled on their Main Contact / Organisation field.
A solution to this is to ensure the Main Contact or Main Organisation field is selected with a Y or N. This pulls out only one job title of the individual, and so on report view, you will not see duplicate(or more) of the Individual record.
However, by doing this through Crystal Report without using any formulas to control the output, a "single individual" who does not belong to any organisation, somehow, will not get selected. Therefore, I will not see this single individual on report view. To resolve this, I remove both the Main Contact/Organisation in the selection criteria.
But then, I am back to square one. I end up viewing individuals who have more than one jobtitle (duplicates or more).
I may not be explaining this clearly but I am new to Crystal Report using ver 8. I have tried playing with the outer joins but no joy. I guess I am wondering if there is a formula to say that..
Even if an individual does not belong to an organisation (or have a null / blank record on their Main Contact/Organisation), it will still get pull out...
...as well as an individual who has a "Y" on their Main Contact/Organisation field.
and be able to export the final result onto Excel...(putting a subreport will bring the desire outcome (tweaked by a formula) but when exporting, there is no data from the subreport part which brings out the jobtitle on the main report).
Hence, I am asking if there is a formula to resolve this as Outer joins and nothing in the table fields I can find which will help achieve the desired outcome.
If clarification is needed, please ask. Apologise if this is not clear as I am still finding my way round through this work database (it's Integra if you are wondering).
Table - INDIVIDUAL
===============
Contact Ref
Individual
Organisation
and other fields
Table - CONTACT
======================
Contact Ref
Individual
Organisation
Jobtitle
Main Contact
Main Organisation
Many thanks!
Edited by wisdom - 08 May 2010 at 10:25am
IP Logged
