Pulling Data Problem 1

[jdaily]

Hi Everyone!

I am running Crystal 8.5 and pulling data from a MS Access 2000 database.

I am having a problem pulling all of the data that I need. I have a report that is pulling patient data and I went and added a 3 more fields of data to pull if the data is there. Before I added these 3 fields I had eleven accounts come up. After I added them there was only two accounts. Not all accounts have data in the 3 particular fields I added.
Is there a way to set it up to pull all the accounts even if the 3 fields are blank? How can I do this?

Thanks,

John

 

[kskid]

When you said "added 3 fields of data", are you saying three more data fields to the table or three more fields to the records selection?

If it's 3 more data fields, then you need to verify the database.

If it's 3 more fields to the record selection, then please post your your record selection.

If any of the tables are outer joined and your selection criteria is against the outer joined table, then you will not get all of the records unless you include the nulls

TABLE_A -> LOJ -> TABLE_B

isnull({TABLE_B.DATA_FIELD} OR {TABLE_B.DATA_FIELD} = "SOMEVALUE"

Without more technical information, we are only guessing.

 

[synapsevampire]

I agree with Witchita, but I'd add to this that you're probably better served to create the Query in the Access database that retrieves the required data, and then base the report on the MS Access Query.

-k

 

[jdaily]

Sorry for the confusion.

I added 3 more fields to the detail section of my crystal report. Not all of my records in the access database have data in these 3 fields. I want to pull all records, based on the grouping and selection criteria I have set, even if the data in any of the 3 fields is blank.
Here is what I am pulling:
I have this report grouped on two different groups, The first is the Patient Type and the second is the Insurance Company. Then I have the patient's record info in the detail section. This report pulls only patient accounts with certain Patient Types and for a specific date range (i.e. Previous Week).
Before I added these 3 new fields to the detail section I showed 11 accounts, after I added them I only showed 2 accounts. I seems that crystal is just pulling the data if all fields are true and ignoring the accounts that have fields that are Null.

Is there anyway to get the accounts even if a particular field is Null?

Thanks,

John

 

[lbass]

This is likely an issue related to joins between multiple tables, so you need to tell us what tables you are drawing your fields from, and which fields you were using at first (from which table) and which you later added. Please refer to the fields by using the convention {table.field}.

-LB

 

[jdaily]

Here are the fields that I have in the detail section before I added the 3 new fields.
Group 1:
{Patient_Information.pat_type}

Group 2:
{Insurance_Companies.ins1_name}

Detail Section:
{Patient_Information.acct_num}
{Patient_Information.pat_name}
{Patient_Information.dob}
{Patient_Information.pat_type}
{Patient_Information.admit}
{Payment_Info.total_charges}
{Payment_Info.acct_bal}

Selection Criteria:
{Denial_Codes.responsible_dept} (will only pull specific departments)
{Patient_Information.pat_type} (will only pull specific patient types)
{Denial_Codes.trans_date} (will only pull these dates from the previous week)

I want to add the following to the detail section:
{Patient_Information.drg_code}
{Patient_Information.prin_diag}
{DRG_Description.drg_descr}

Now, without the 3 new fields I get 11 accounts. With the 3 new fields I get 2 accounts. I think it is pulling the data when the 3 new fields are true. I don't care if the 3 new ones have data or not because I know that most of the accounts in my database don't have data for those 3 fields I want to add.

Can I select the records even if a field is null?

 

[lbass]

Go to database->visual linking expert and find the DRG_Description table. I'm guessing this table might be linked to the Patient Information table on the drg_code. Change the link FROM the Patient Information table to the DRG_Description table to a left join. This should solve the problem.

-LB

 

[jdaily]

OUTSTANDING!

Thank you lbass! It worked perfect!

John