Insert Into Statement

[Learn2010]

I am trying to convert the SQL behind an Access query and use it in a Docmd.RunSQL "" query. I have made several attempts and cannot figure it out. Can someone help me with this? I have two tables linked on the PatientID.

START OF CODE
INSERT INTO tblDiagnosesHold ( Diagnosis, PatientID )
SELECT [Diagnosis] & " (" & [ICD9Code] & ")" AS Expr, [tblPatientsHold].PatientID
FROM tblPatientsHold INNER JOIN tblDimDiagnoses ON [tblPatientsHold].PatientID=tblDimDiagnoses.PatientID;
END OF CODE

Thank you.

 

[theDBguy]

Hi. You could try where you have quotes, replace them with two quotes.
Sent from phone...

 

[The_Doc_Man]

Is there a chance that the [Diagnosis] field exists in both tblPatientsHold and tblDimDiagnoses? Because if so, you need to qualify from which table it originated. Just like you qualified PatientID in the SELECT line because IT appears twice. Ditto [ICD9Code]. If EITHER ONE is ambiguous that would cause a problem.

 

[Learn2010]

Hi. You could try where you have quotes, replace them with two quotes.
Sent from phone...

Thank you

 

[Learn2010]

I have several queries like this. I made a change to the query to simplify it and came up with this. How would this work? Thank you.

START OF CODE
INSERT INTO tblDiagnosesHold ( Diagnosis, ICD9Code, ICD10Code, PatientID )
SELECT tblDimDiagnoses.Diagnosis, tblDimDiagnoses.ICD9Code, tblDimDiagnoses.ICD10Code, tblPatientsHold.PatientID
FROM tblPatientsHold INNER JOIN tblDimDiagnoses ON tblPatientsHold.PatientID = tblDimDiagnoses.PatientID;
END OF CODE

 

[Cronk]

Your code should read something like
dim strSQL as string strSQL="INSERT INTO tblDiagnosesHold ( Diagnosis, ICD9Code, ICD10Code, PatientID ) SELECT tblDimDiagnoses.Diagnosis, lDimDiagnoses.ICD9Code, tblDimDiagnoses.ICD10Code, tblPatientsHold.PatientID FROM tblPatientsHold INNER JOIN tblDimDiagnoses ON tblPatientsHold.PatientID = tblDimDiagnoses.PatientID; " docmd.runsql strSQL

If you want to split the lines of SQL, then
dim strSQL as string strSQL="INSERT INTO tblDiagnosesHold ( Diagnosis, ICD9Code, ICD10Code, PatientID ) " _ & "SELECT tblDimDiagnoses.Diagnosis, lDimDiagnoses.ICD9Code, tblDimDiagnoses.ICD10Code, tblPatientsHold.PatientID " _ & "FROM tblPatientsHold INNER JOIN tblDimDiagnoses ON tblPatientsHold.PatientID = tblDimDiagnoses.PatientID; " docmd.runsql strSQL

That sql will duplicate every record in tblDiagnosesHold with a matching PatientID record in tblDimDiagnoses. If you only want to insert a record for the current form view (assuming only one patient record is being displayed), then
dim strSQL as string strSQL="INSERT INTO tblDiagnosesHold ( Diagnosis, ICD9Code, ICD10Code, PatientID ) " _ & "SELECT tblDimDiagnoses.Diagnosis, lDimDiagnoses.ICD9Code, tblDimDiagnoses.ICD10Code, tblPatientsHold.PatientID " _ & "FROM tblDimDiagnoses WHERE tblDimDiagnoses.PatientID= " & Me.PatientID" docmd.runsql strSQL

 

[jdraw]

In addition to Cronk's comment, you may want to research
Currentdb.Execute strSQL,dbFailOnError

 

[theDBguy]

Just FYI, this is crossposted here.

 

[Learn2010]

I found this on a crosspost and it doe answer my question. I thought it would speed it up. If not, I will use what I have.

"Switching a saved query to VBA will in no way speed it up."

Thank you.

 

[Pat Hartman]

Mushing separate data attributes into a single field is poor practice. It would be far better to add a separate column to hold the ICD9 code. And then there's the problem that the ICD9 codes have been superseded by ICD10 because it is very important to know the color of the frog that poisoned you..