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.OperationSeqID
       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.PatientCaseID
       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
GPSPOWAsked:
Who is Participating?
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.

[ fanpages ]IT Services ConsultantCommented:
Hi Glen,

Have you tried converting the data type within the JOIN statement?

---
JOIN      SchOrPatCaseActualOps AS AOPS
                  On AOPS.PatientCaseID = ORPAT.PatientCaseID
                  AND ICD.ProcedureSeqID = CLng(AOPS.ActualOpID)
---

or

---
JOIN      SchOrPatCaseActualOps AS AOPS
                  On AOPS.PatientCaseID = ORPAT.PatientCaseID
                  AND CStr(ICD.ProcedureSeqID) = AOPS.ActualOpID
---

BFN,

fp.
0

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
PortletPaulfreelancerCommented:
the suggestion above is a good one, I'd just like to know if you are going to have a nightmare like:

AOPS.ActualOpID
fred
wilma
barney
betty

i.e.  cases where no amount of type changing will permit a join

should AOPS.ActualOpID be changed to a numeric field?
0
GPSPOWAuthor Commented:
The Text field will always have an integer for a value.

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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PortletPaulfreelancerCommented:
>>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.
0
GPSPOWAuthor Commented:
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
0
[ fanpages ]IT Services ConsultantCommented:
You are very welcome, Glen.

Good luck with the rest of your project.
0
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
Microsoft Applications

From novice to tech pro — start learning today.