GPSPOW
asked on
MS Access equivalent JOIN from SQL
FROM AbsOperationProcedures AS ICD
INNER JOIN AbstractData as ABS
ON ICD.VisitID = ABS.VisitID
JOIN NHSN_ICD9 as NICD
ON NICD.[ICD-9-CM] = ICD.ProcedureCode
JOIN AbsOperations AS ABO
ON ABO.VisitID=ICD.VisitID
AND ABO.OperationSeqID=ICD.Ope rationSeqI D
JOIN SchOrPatCases AS ORPAT
ON ORPAT.VisitID = ABO.VisitID
JOIN SchOrPatCaseActualOps AS AOPS
On AOPS.PatientCaseID = ORPAT.PatientCaseID
AND ICD.ProcedureSeqID = AOPS.ActualOpID
JOIN SchPatTimesOp AS OPTIMES
ON AOPS.PatientCaseID = OPTIMES.CaseID
JOIN SchOrPatCasePostOpDetails AS ASA
ON ORPAT.PatientCaseID=ASA.Pa tientCaseI D
JOIN SchOrPatCasePostOpDetails AS OPD
ON ORPAT.PatientCaseID = OPD.PatientCaseID
LEFT JOIN AbsQueriesMult AS MQR
ON ICD.VisitID = MQR.VisitID
AND MQR.GroupResponse = 'ED.TRAMEC1'
JOIN AdmVisitOrders AS AVO
ON ICD.VisitID = AVO.VisitID
LEFT JOIN AbsDrgDiagnoses AS DRG
ON ICD.VisitID=DRG.VisitID
AND DRG.Diagnosis BETWEEN N'250' AND N'250.93'
Here are the JOIN statements from a SQL Stored Procedure I am trying to emulate in MS Access.
All the JOINS work except for :
JOIN SchOrPatCaseActualOps AS AOPS
On AOPS.PatientCaseID = ORPAT.PatientCaseID
AND ICD.ProcedureSeqID = AOPS.ActualOpID
The problem is that ICD.ProcedureSeqID is NUMERIC and AOPS.ActualOpID is a Text field.
Any suggestions on making this work in MS Access?
Thanks
Glen
INNER JOIN AbstractData as ABS
ON ICD.VisitID = ABS.VisitID
JOIN NHSN_ICD9 as NICD
ON NICD.[ICD-9-CM] = ICD.ProcedureCode
JOIN AbsOperations AS ABO
ON ABO.VisitID=ICD.VisitID
AND ABO.OperationSeqID=ICD.Ope
JOIN SchOrPatCases AS ORPAT
ON ORPAT.VisitID = ABO.VisitID
JOIN SchOrPatCaseActualOps AS AOPS
On AOPS.PatientCaseID = ORPAT.PatientCaseID
AND ICD.ProcedureSeqID = AOPS.ActualOpID
JOIN SchPatTimesOp AS OPTIMES
ON AOPS.PatientCaseID = OPTIMES.CaseID
JOIN SchOrPatCasePostOpDetails AS ASA
ON ORPAT.PatientCaseID=ASA.Pa
JOIN SchOrPatCasePostOpDetails AS OPD
ON ORPAT.PatientCaseID = OPD.PatientCaseID
LEFT JOIN AbsQueriesMult AS MQR
ON ICD.VisitID = MQR.VisitID
AND MQR.GroupResponse = 'ED.TRAMEC1'
JOIN AdmVisitOrders AS AVO
ON ICD.VisitID = AVO.VisitID
LEFT JOIN AbsDrgDiagnoses AS DRG
ON ICD.VisitID=DRG.VisitID
AND DRG.Diagnosis BETWEEN N'250' AND N'250.93'
Here are the JOIN statements from a SQL Stored Procedure I am trying to emulate in MS Access.
All the JOINS work except for :
JOIN SchOrPatCaseActualOps AS AOPS
On AOPS.PatientCaseID = ORPAT.PatientCaseID
AND ICD.ProcedureSeqID = AOPS.ActualOpID
The problem is that ICD.ProcedureSeqID is NUMERIC and AOPS.ActualOpID is a Text field.
Any suggestions on making this work in MS Access?
Thanks
Glen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>The Text field will always have an integer for a value.
so, why not make it numeric? is there any reason for keeping it text?
every time you wish to join to that table you are doomed to using a conversion, not only is that slower than it could be, it will be frustrating to remember - and anyone who may need to maintain this might not know it is needed until they hit an error.
just a thought, but if it were me, I'd make the field match the type needed.
so, why not make it numeric? is there any reason for keeping it text?
every time you wish to join to that table you are doomed to using a conversion, not only is that slower than it could be, it will be frustrating to remember - and anyone who may need to maintain this might not know it is needed until they hit an error.
just a thought, but if it were me, I'd make the field match the type needed.
ASKER
I will have to think about designing something else. Changing the format of the field is not an option. I am bringing the table in from a SQL database that I have no control over.
Thanks to all who gave me a lot of valuable information.
Glen
Thanks to all who gave me a lot of valuable information.
Glen
You are very welcome, Glen.
Good luck with the rest of your project.
Good luck with the rest of your project.
ASKER
Pertaining to Fanpages suggestions:
Since this join is going to be in MS Access, can you modify the SQL statement in the Query Design?
Thanks
Glen