Insert Into Statement (1 Viewer)

Learn2010

Registered User.
Local time
Today, 14:49
Joined
Sep 15, 2010
Messages
415
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

I’m here to help
Staff member
Local time
Today, 11:49
Joined
Oct 29, 2018
Messages
11,886
Hi. You could try where you have quotes, replace them with two quotes.
Sent from phone...
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 13:49
Joined
Feb 28, 2001
Messages
17,719
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

Registered User.
Local time
Today, 14:49
Joined
Sep 15, 2010
Messages
415
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

Registered User.
Local time
Tomorrow, 04:49
Joined
Jul 4, 2013
Messages
2,416
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

Super Moderator
Staff member
Local time
Today, 14:49
Joined
Jan 23, 2006
Messages
13,310
In addition to Cronk's comment, you may want to research
Currentdb.Execute strSQL,dbFailOnError
 

Learn2010

Registered User.
Local time
Today, 14:49
Joined
Sep 15, 2010
Messages
415
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

Super Moderator
Staff member
Local time
Today, 14:49
Joined
Feb 19, 2002
Messages
29,472
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..
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom