Query check previous location vs service inconsistent

Data Integrity Checks
Summary:	patients with Previous Location field in medicine must have Previous Service field medicine; ICU (except STB ACCU, STB CICU/ICCS) must have previous service "critical care".
Related:	Previous Location field, Previous Service field
Firmness:	hard check
Timing:
App:	CCMDB.accdb
Coding:	function previous_location_vs_service_inconsistent, query check_previous_location_vs_service_inconsistent
Uses L Problem table:	not relevant for this app
Status:	implemented
Implementation Date:	date change was rolled out / first applied
Backlogged:	true

• Cargo

• SMW

• Categories:  

• form:

Additional Information

Error in function previous_location_vs_service_inconsistent is:

"Previous Service and Previous Location must correspond, ie from: medicine, prevServ: medicine; from ICU, prevServ: critical care; from CCU/ACCU, prevServ: Cardology; from ICCS, prevServ Cardiac Surgery. "

Query check_previous_location_vs_service_inconsistent will show the following as errors:

SELECT L_Log.Pat_ID, s_dispo_previous.location_name, s_dispo_previous.Hospital, s_dispo_previous.Ward, s_previous_service.previous_service

FROM (L_Log INNER JOIN s_dispo AS s_dispo_previous ON L_Log.Previous_Location = s_dispo_previous.dispo_ID) INNER JOIN s_previous_service ON L_Log.Previous_Service = s_previous_service.previous_service_ID

WHERE

(((s_dispo_previous.Hospital)<>"HSC") AND ((s_previous_service.previous_service)<>"Medicine" And (s_previous_service.previous_service)<>"Family Medicine") AND ((s_dispo_previous.loc_type)="Ward") AND ((s_dispo_previous.s_location)=True))

OR (((s_dispo_previous.Ward)<>"CICU" And (s_dispo_previous.Ward)<>"ACCU" And (s_dispo_previous.Ward)<>"ICCS") AND ((s_previous_service.previous_service)<>"Critical Care") AND ((s_dispo_previous.loc_type)="ICU") AND ((s_dispo_previous.s_location)=True))

OR (((s_dispo_previous.Hospital)="HSC") AND ((s_previous_service.previous_service)<>"Medicine" And (s_previous_service.previous_service)<>"Nephrology") AND ((s_dispo_previous.loc_type)="Ward") AND ((s_dispo_previous.s_location)=True))

OR (((s_dispo_previous.Ward)="ACCU") AND ((s_previous_service.previous_service)<>"Critical Care" And (s_previous_service.previous_service)<>"Cardiology") AND ((s_dispo_previous.loc_type)="ICU") AND ((s_dispo_previous.s_location)=True))

OR (((s_dispo_previous.Ward)="ICCS") AND ((s_previous_service.previous_service)<>"Cardiac surgery") AND ((s_dispo_previous.loc_type)="ICU") AND ((s_dispo_previous.s_location)=True))

OR (((s_dispo_previous.Ward)="CICU") AND ((s_previous_service.previous_service)<>"Cardiac Sciences") AND ((s_dispo_previous.loc_type)="ICU") AND ((s_dispo_previous.s_location)=True));

Log

2021-01-05 - expect CICU to use newly added option "Cardiac Sciences"