bfuchs
asked on
Query listing missing fields needed.
Hi Experts,
I have the following query listing all records with missing data (in those fields listed)
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_Descrip tion is null, field should be MissingData:"Urine_Stool_V olume_Desc ription, Pain_Score" (only if condition/s above are true of course...)
Thanks
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 modifications1- 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_Descrip
Thanks
ASKER
Hi,
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
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_Goa l 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_De scription Is Null) OR
(SNVN.Urine_Stool_Volume_D escription Is Null));
SNVN.Nurse_User_ID_num_SNV
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
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_Goa
(SNVN.Temp Is Null) OR
(SNVN.Pulse Is Null) OR
(SNVN.Skin_Temp Is Null) OR
(SNVN.Abdominal_Assessment
(SNVN.GU_Incontinent Is Null) OR
(SNVN.GU_Incontinent='yes'
(SNVN.Urine_Stool_Volume_D
@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.
Is the SQL going to be run in an Access database? If so, the SQL will need to conform to the Jet engine standards.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Scott
I asked my question because you had posted SQL with CASE clauses.
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).
I understand, since CASE isn't allowed in Access SQL (although I think it is allowed in Access VBA code, weird).
ASKER
since CASE isn't allowed in Access SQLActually 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:
And results:
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.
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
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;
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
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.
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
ASKER
Thanks to all participants!
You could use an IIF statement in Access. Been awhile since I worked in Access, but something like:
Open in new window