Query listing missing fields needed.

bfuchs
bfuchs used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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

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#a42742915
Thanks,
Ben
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
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));
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

Top Expert 2014

Commented:
@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.
@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
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Here's the SQL Server version.  For Access, you'd have to convert the syntax back to Access, I don't know Access syntax well enough to do that.

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 (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Nurse_User_ID_num_SNV ORDER BY Visit_Date DESC) AS row_num
    FROM Skilled_Nursing_Visit_Note
    WHERE Status='completed' AND
          Visit_Date>GetDate()-90 AND
          ((Client_Last_Name Is Null) OR
           (Client_First_Name Is Null) OR
           (Date_Of_Birth Is Null) OR
           (Shift_From_Hour Is Null) OR
           (Shift_To_Hour Is Null) OR
           (Shift_From_Minute Is Null) OR
           (Shift_To_Minute Is Null) OR
           (Purpose_Of_Visit_Goal Is Null) OR
           (Temp Is Null) OR
           (Pulse Is Null) OR
           (Skin_Temp Is Null) OR
           (Abdominal_Assessment Is Null) OR
           (GU_Incontinent Is Null) OR
           (GU_Incontinent='yes' AND Urine_Stool_Volume_Description Is Null) OR
           (Urine_Stool_Volume_Description Is Null))
) AS SNVN
WHERE row_num = 1
Top Expert 2014

Commented:
@Scott

I asked my question because you had posted SQL with CASE clauses.
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
@aikimark:

I understand, since CASE isn't allowed in Access SQL (although I think it is allowed in Access VBA code, weird).
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
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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.
Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
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.
Top Expert 2014

Commented:
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

Thanks to all participants!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial