troubleshooting Question

Query listing missing fields needed.

Avatar of bfuchs
bfuchsFlag for United States of America asked on
DatabasesMicrosoft AccessMicrosoft SQL ServerSQL
13 Comments1 Solution98 ViewsLast Modified:
Hi Experts,

I have the following query listing all records with missing data (in those fields listed)
SELECT Skilled_Nursing_Visit_Note.Nurse_Signature_Last_Name, Skilled_Nursing_Visit_Note.Nurse_Signature_First_Name, Skilled_Nursing_Visit_Note.Nurse_User_ID_num_SNV, Skilled_Nursing_Visit_Note.Visit_Date, Skilled_Nursing_Visit_Note.SNV_ID
FROM Skilled_Nursing_Visit_Note
WHERE (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90 And (Skilled_Nursing_Visit_Note.Visit_Date) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Client_Last_Name) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Client_First_Name) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Date_Of_Birth) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Shift_From_Hour) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Shift_To_Hour) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Shift_From_Minute) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Shift_To_Minute) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Purpose_Of_Visit_Goal) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Temp) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Pulse) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Skin_Temp) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Abdominal_Assessment) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.GU_Incontinent) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.GU_Incontinent) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.GU_Incontinent)="yes") AND ((Skilled_Nursing_Visit_Note.Urine_Stool_Volume_Description) Is Null)) OR (((Skilled_Nursing_Visit_Note.Status)="completed") AND ((Skilled_Nursing_Visit_Note.Visit_Date)>Date()-90) AND ((Skilled_Nursing_Visit_Note.Urine_Stool_Volume_Description) Is Null));
Would need the following modifications

1- only list one record per Nurse_User_ID_num_SNV, the other fields should be taken from last record of this Nurse_User_ID_num_SNV (last according to visit_date).

2- add a field listing the field names that are missing, so for example if Pain_Score is null and Urine_Stool_Volume_Description is null, field should be MissingData:"Urine_Stool_Volume_Description, Pain_Score" (only if condition/s above are true of course...)

Thanks
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 13 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 13 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros