Solved

Need results with a NULL field.

Posted on 2008-10-10
3
166 Views
Last Modified: 2008-11-15
I have a report that shows me labs my company has run for a specific time period. The labs are usually run for a surgeon but not always and thus the surgeon (tbldoctors.lastname) field is sometimes NULL. I need to get the results for a specific time period regardless of the fact that this field might be null so that we can see all labs performed. I am attaching the SQL statement created by Crystal below as a CODE SNIPPET

My results are as follows for 6/1/08 - 10/10/08
-1- If I remove the tbldoctors.lastname field, I get 20 labs for the time frame
-2- If I leave the tbldoctors.lastname field, I get 13 labs for the same time period

I tried the following formula field to try to get the NULL fields to return "n/a" but I get the same 13 results:

if isnull ({tblDoctors.LastName}) then "n/a" else
{tblDoctors.LastName}

Any help would be GREATLY APPRECIATED.
SELECT "tblCadaverLabs"."LabID", "tblLabLocations"."LocationName", "tblCadaverLabs"."LabDate", "tblMISUsers"."Name", "tblCadaverLabs"."LabStatus", "tblDoctors"."LastName"

 FROM   (((("ATR0605_003LIVE"."dbo"."tblCadaverLabs" "tblCadaverLabs" INNER JOIN "ATR0605_003LIVE"."dbo"."tblLabLocations" "tblLabLocations" ON "tblCadaverLabs"."LocationID"="tblLabLocations"."LocationID") INNER JOIN "ATR0605_003LIVE"."dbo"."tblLabSupport" "tblLabSupport" ON "tblCadaverLabs"."LabID"="tblLabSupport"."LabID") INNER JOIN "ATR0605_003LIVE"."dbo"."tblLabDoctors" "tblLabDoctors" ON "tblCadaverLabs"."LabID"="tblLabDoctors"."LabID") INNER JOIN "ATR0605_003LIVE"."dbo"."tblDoctors" "tblDoctors" ON "tblLabDoctors"."DoctorID"="tblDoctors"."DoctorID") INNER JOIN "ATR0605_003LIVE"."dbo"."tblMISUsers" "tblMISUsers" ON "tblLabSupport"."UserID"="tblMISUsers"."UserID"

 WHERE  ("tblCadaverLabs"."LabDate">={ts '2008-06-01 00:00:00'} AND "tblCadaverLabs"."LabDate"<{ts '2008-10-11 00:00:00'}) AND "tblCadaverLabs"."LabStatus"='original'

 ORDER BY "tblCadaverLabs"."LabDate"

Open in new window

0
Comment
Question by:dsabine
3 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 22693977
Try using a LEFT OUTER JOIN rather than inner joins.

A left outer join gets all the records in the left table tblCadaverLabs and all matching records and nulls from the other table.

mlmcc
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now