Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

asked on

Query listing missing fields needed.

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));

Open in new window

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
Avatar of Dustin Saunders
Dustin Saunders
Flag of United States of America image

Is this auto-generated SQL?  Very hard to read...  and looks like you could nest your logic to shorten the whole thing.

You could use an IIF statement in Access.  Been awhile since I worked in Access, but something like:
IIF(FieldName Is Null, "FieldName","") & ....  AS MissingFields

Open in new window

Avatar of bfuchs

ASKER

Hi,
Is this auto-generated SQL?  Very hard to read...
Yes, its whats been generated from Access designer...
I have posted here the create table script for that if needed in SQL...
https://www.experts-exchange.com/questions/29127418/Error-running-append-query.html?anchor=a42742915¬ificationFollowed=218803754&anchorAnswerId=42742915#a42742915
Thanks,
Ben
SELECT SNVN.Nurse_Signature_Last_Name, SNVN.Nurse_Signature_First_Name,
    SNVN.Nurse_User_ID_num_SNV, SNVN.Visit_Date, SNVN.SNV_ID,
    STUFF(
    CASE WHEN SNVN.Client_Last_Name Is Null THEN ', Client_Last_Name' ELSE '' END +
    CASE WHEN SNVN.Client_First_Name Is Null THEN ', Client_First_Name' ELSE '' END +
    ...and so on, for every column checked for NULL in the WHERE clause below...
    , 1, 2, '') AS MissingData
FROM Skilled_Nursing_Visit_Note SNVN
WHERE SNVN.Status="completed" AND
      SNVN.Visit_Date>Date()-90 AND
      ((SNVN.Client_Last_Name Is Null) OR
       (SNVN.Client_First_Name Is Null) OR
       (SNVN.Date_Of_Birth Is Null) OR
       (SNVN.Shift_From_Hour Is Null) OR
       (SNVN.Shift_To_Hour Is Null) OR
       (SNVN.Shift_From_Minute Is Null) OR
       (SNVN.Shift_To_Minute Is Null) OR
       (SNVN.Purpose_Of_Visit_Goal Is Null) OR
       (SNVN.Temp Is Null) OR
       (SNVN.Pulse Is Null) OR
       (SNVN.Skin_Temp Is Null) OR
       (SNVN.Abdominal_Assessment Is Null) OR
       (SNVN.GU_Incontinent Is Null) OR
       (SNVN.GU_Incontinent='yes' AND SNVN.Urine_Stool_Volume_Description Is Null) OR
       (SNVN.Urine_Stool_Volume_Description Is Null));
@Ben

Is the SQL going to be run in an Access database?  If so, the SQL  will need to conform to the Jet engine standards.
Avatar of bfuchs

ASKER

@Dustin, @Scott,
How can I modify it to address question #1.
@Aikimark,
I prefer Access, however can export to SQL and run it from SSMS (or Access pass-through) if needed.
Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Scott

I asked my question because you had posted SQL with CASE clauses.
@aikimark:

I understand, since CASE isn't allowed in Access SQL (although I think it is allowed in Access VBA code, weird).
Avatar of bfuchs

ASKER

since CASE isn't allowed in Access SQL
Actually In Access I would use the IIF what Distin suggested, its just the ROW_NUMBER() OVER(PARTITION BY part that does not exists there..
Thanks,
Ben
You can get a Row_Number from Access with an unequal self join, not sure how else to do is (as mentioned, been a couple years since I used Access to write Queries).

Here's my Table.  And query:
ID	Nurse_Id	When
1	1	11/8/2018
2	1	11/14/2018
3	1	11/22/2018
4	2	11/6/2018
5	2	11/22/2018

Open in new window


SELECT x.Nurse_Id AS Expr1, x.When, Count(*) AS ["Row"]
FROM Table2 AS x INNER JOIN Table2 AS x2 ON (x.When <= x2.When) AND (x.Nurse_Id = x2.Nurse_Id)
GROUP BY x.Nurse_Id, x.When;

Open in new window


And results:
Expr1	When	"""Row"""
1	11/8/2018	3
1	11/14/2018	2
1	11/22/2018	1
2	11/6/2018	2
2	11/22/2018	1

Open in new window


Then you can join the result of that to get the most recent record number.  So long as the date field isn't the same time, you shouldn't get dupe results, otherwise you'd want to add another condition to sort them.  Another Expert might have a better way to do that in Access, but it's an approach.
One other note--

You could just take the SQL query and create a new View in the SQL database.  Then in Access, just grab that view.
Here's your missing fields expression:
Mid(
IIF(IsNull([SNV_ID]),",SNV_ID","") & 
IIF(IsNull([Client_Last_Name]),",Client_Last_Name","") & 
IIF(IsNull([Visit_Date]),",Visit_Date","") & 
IIF(IsNull([Date_Of_Birth]),",Date_Of_Birth","") & 
IIF(IsNull([Medical_Record_Number]),",Medical_Record_Number","") & 
IIF(IsNull([Shift_From_Hour]),",Shift_From_Hour","") & 
IIF(IsNull([Shift_To_Hour]),",Shift_To_Hour","") & 
IIF(IsNull([Purpose_Of_Visit_Goal]),",Purpose_Of_Visit_Goal","") & 
IIF(IsNull([Temp]),",Temp","") & 
IIF(IsNull([Pulse]),",Pulse","") & 
IIF(IsNull([RR]),",RR","") & 
IIF(IsNull([BP]),",BP","") & 
IIF(IsNull([O2_Saturation]),",O2_Saturation","") & 
IIF(IsNull([O2_In_Use]),",O2_In_Use","") & 
IIF(IsNull([Respiratory_Lung_Sounds]),",Respiratory_Lung_Sounds","") & 
IIF(IsNull([Respiratory_Breathing_Pattern]),",Respiratory_Breathing_Pattern","") & 
IIF(IsNull([Bipap]),",Bipap","") & 
IIF(IsNull([Cpap]),",Cpap","") & 
IIF(IsNull([Vent]),",Vent","") & 
IIF(IsNull([Vent_BIPAP_Settings]),",Vent_BIPAP_Settings","") & 
IIF(IsNull([Cardiovascular_Skin_Color]),",Cardiovascular_Skin_Color","") & 
IIF(IsNull([Skin_Temp]),",Skin_Temp","") & 
IIF(IsNull([Cardiovascular_Edema]),",Cardiovascular_Edema","") & 
IIF(IsNull([Neuro_Alert]),",Neuro_Alert","") & 
IIF(IsNull([Neuro_Oriented]),",Neuro_Oriented","") & 
IIF(IsNull([Neuro_Self_Directing]),",Neuro_Self_Directing","") & 
IIF(IsNull([Neuro_Dev_Delay]),",Neuro_Dev_Delay","") & 
IIF(IsNull([Neuro_Deficits]),",Neuro_Deficits","") & 
IIF(IsNull([Neuro_Notes]),",Neuro_Notes","") & 
IIF(IsNull([GI_Oral_Tube_Feeding]),",GI_Oral_Tube_Feeding","") & 
IIF(IsNull([Current_Feeds]),",Current_Feeds","") & 
IIF(IsNull([Gastric_Residuals_Present]),",Gastric_Residuals_Present","") & 
IIF(IsNull([Residual_Volume]),",Residual_Volume","") & 
IIF(IsNull([MD_Called]),",MD_Called","") & 
IIF(IsNull([Abdominal_Assessment]),",Abdominal_Assessment","") & 
IIF(IsNull([Feeding_Tolerance]),",Feeding_Tolerance","") & 
IIF(IsNull([GU_Incontinent]),",GU_Incontinent","") & 
IIF(IsNull([Number_Of_Diaper_Changes]),",Number_Of_Diaper_Changes","") & 
IIF(IsNull([Urine_Stool_Volume_Description]),",Urine_Stool_Volume_Description","") & 
IIF(IsNull([Bedbound]),",Bedbound","") & 
IIF(IsNull([WheelChair]),",WheelChair","") & 
IIF(IsNull([Ambulatory]),",Ambulatory","") & 
IIF(IsNull([Mobility_Aids]),",Mobility_Aids","") & 
IIF(IsNull([Contractures]),",Contractures","") & 
IIF(IsNull([Musculoskeletal_Notes]),",Musculoskeletal_Notes","") & 
IIF(IsNull([Pain_Score]),",Pain_Score","") & 
IIF(IsNull([Pain_Notes]),",Pain_Notes","") & 
IIF(IsNull([Medication_Changes]),",Medication_Changes","") & 
IIF(IsNull([Medication_Changes_2]),",Medication_Changes_2","") & 
IIF(IsNull([Integument_Skin_Assessment]),",Integument_Skin_Assessment","") & 
IIF(IsNull([Skin_Care_Measures_Taken]),",Skin_Care_Measures_Taken","") & 
IIF(IsNull([Home_Environment_Safety]),",Home_Environment_Safety","") & 
IIF(IsNull([Treatments_Administered]),",Treatments_Administered","") & 
IIF(IsNull([Patient_Response]),",Patient_Response","") & 
IIF(IsNull([Instructions]),",Instructions","") & 
IIF(IsNull([Understanding]),",Understanding","") & 
IIF(IsNull([Plan_Goal]),",Plan_Goal","") & 
IIF(IsNull([Nurse_Signature_Last_Name]),",Nurse_Signature_Last_Name","") & 
IIF(IsNull([Signature]),",Signature","") & 
IIF(IsNull([Date_Signed]),",Date_Signed","") & 
IIF(IsNull([Nurse_Name_Stamp_SNV]),",Nurse_Name_Stamp_SNV","") & 
IIF(IsNull([Nurse_User_ID_num_SNV]),",Nurse_User_ID_num_SNV","") & 
IIF(IsNull([Medication_Changes_Yes]),",Medication_Changes_Yes","") & 
IIF(IsNull([Acknowledge_Accuracy]),",Acknowledge_Accuracy","") & 
IIF(IsNull([Trach]),",Trach","") & 
IIF(IsNull([CCM_Informed]),",CCM_Informed","") & 
IIF(IsNull([Tube_Type]),",Tube_Type","") & 
IIF(IsNull([Reviewed_Status]),",Reviewed_Status","") & 
IIF(IsNull([Reviewed_By]),",Reviewed_By","") & 
IIF(IsNull([Reviewed_stamp]),",Reviewed_stamp","") & 
IIF(IsNull([Client_First_Name]),",Client_First_Name","") & 
IIF(IsNull([Nurse_Signature_First_Name]),",Nurse_Signature_First_Name","") & 
IIF(IsNull([Shift_From_Minute]),",Shift_From_Minute","") & 
IIF(IsNull([Shift_To_Minute]),",Shift_To_Minute","") & 
IIF(IsNull([Patient_Unable_To_Sign]),",Patient_Unable_To_Sign","") & 
IIF(IsNull([Reason]),",Reason","") & 
IIF(IsNull([Status]),",Status","")
, 2) As MissingFields

Open in new window

Avatar of bfuchs

ASKER

Thanks to all participants!