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
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
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

bfuchsAuthor Commented:
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 DBACommented:
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));
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

aikimarkCommented:
@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.
bfuchsAuthor Commented:
@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
Scott PletcherSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aikimarkCommented:
@Scott

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

I understand, since CASE isn't allowed in Access SQL (although I think it is allowed in Access VBA code, weird).
bfuchsAuthor Commented:
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 SaundersDirector of OperationsCommented:
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 SaundersDirector of OperationsCommented:
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.
aikimarkCommented:
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

bfuchsAuthor Commented:
Thanks to all participants!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.