Appends with Diff Result (1 Viewer)

danbl

Registered User.
Local time
Today, 14:45
Joined
Mar 27, 2006
Messages
262
I am not experienced with SQL :( The original appends had a date condition in the where statement. The appends below represent my attempt to append data based now on a physician mane. The first append captures the correct number of records.

Visit Append
INSERT INTO t_Visit ( VisitDate, DOB, Physician )
SELECT t_Advanced_Directive1.Date, t_Advanced_Directive1.DOB, t_Physician.DrID
FROM t_Advanced_Directive1 INNER JOIN t_Physician ON t_Advanced_Directive1.Physician=t_Physician.Physician
WHERE (((t_Advanced_Directive1.DOB) Is Not Null)) And t_Advanced_Directive1.Physician Like “J. Mc*”
ORDER BY t_Advanced_Directive1.Date;


These appends when I apply the same Logic do not pull any records?? I have attempted to use the DrId for each involved but no luck.
Help would be immensely Appreciated. :)

Append AD
INSERT INTO t_Advanced_Directive ( [PAT at AMAASS], Q1, NA_1, Q2, NA_2, Q3, NA_3, [PAT by Phone], Q5, NA_4, Q6, NA_5, Q7, NA_6, [PAT Phone Cmt], [All Patients], Q8, NA_7, Q9, NA_8, Preoperative, Q10, NA_9, Q11, NA_10, Interoperative, Q12, NA_11, Q13, NA_12, Postoperative, Q14, NA_13, [ADVANCE DIRECTIVES IMPLIMENTED], NA_14, Comments, ADCom, vil )
SELECT t_Advanced_Directive1.[PAT at AMAASS], t_Advanced_Directive1.Q1, t_Advanced_Directive1.NA_1, t_Advanced_Directive1.Q2, t_Advanced_Directive1.NA_2, t_Advanced_Directive1.Q3, t_Advanced_Directive1.NA_3, t_Advanced_Directive1.[PAT by Phone], t_Advanced_Directive1.Q5, t_Advanced_Directive1.NA_4, t_Advanced_Directive1.Q6, t_Advanced_Directive1.NA_5, t_Advanced_Directive1.Q7, t_Advanced_Directive1.NA_6, t_Advanced_Directive1.[PAT Phone Cmt], t_Advanced_Directive1.[All Patients], t_Advanced_Directive1.Q8, t_Advanced_Directive1.NA_7, t_Advanced_Directive1.Q9, t_Advanced_Directive1.NA_8, t_Advanced_Directive1.Preoperative, t_Advanced_Directive1.Q10, t_Advanced_Directive1.NA_9, t_Advanced_Directive1.Q11, t_Advanced_Directive1.NA_10, t_Advanced_Directive1.Interoperative, t_Advanced_Directive1.Q12, t_Advanced_Directive1.NA_11, t_Advanced_Directive1.Q13, t_Advanced_Directive1.NA_12, t_Advanced_Directive1.Postoperative, t_Advanced_Directive1.Q14, t_Advanced_Directive1.NA_13, t_Advanced_Directive1.[ADVANCE DIRECTIVES IMPLIMENTED], t_Advanced_Directive1.NA_14, t_Advanced_Directive1.Comments, t_Advanced_Directive1.ADCom, t_Visit.vid
FROM t_Physician INNER JOIN (t_Visit INNER JOIN t_Advanced_Directive1 ON (t_Visit.VisitDate=t_Advanced_Directive1.Date) AND (t_Visit.DOB=t_Advanced_Directive1.DOB)) ON (t_Physician.DrID=t_Visit.Physician) AND (t_Physician.Physician=t_Advanced_Directive1.Physician)
WHERE (((t_Advanced_Directive1.DOB) Is Not Null)) And t_Advanced_Directive1.Physician Like "J. McG*";

Append PO
INSERT INTO [t_Perioperative Data] ( [Peroperative Indicator], PQ1, PNA_1, PQ2, PNA_2, PQ3, PNA_3, PQ4, PNA_4, PQ4a, PNA_4a, PQ5, PNA_5, PQ5a, PNA_5a, PQ6, PNA_6, Interoperative, PQ8, PNA_8, PQ9, PNA_9, PQ10, PNA_10, PQ11, PNA_11, PQ12, PNA_12, PQ13, PNA_13, PQ13a, PNA13a, PQ14, PNA_14, Postoperative, PQ15, PNA_15, PQ16, PNA_16, Comments, PQ17, PQ18, PQ19, PQ20, PNA_20, PQ21, PQ22, AbxCom, HebCom, EquipCom, RMCom, vil )
SELECT [t_Perioperative Data1].[Peroperative Indicator], [t_Perioperative Data1].Q1, [t_Perioperative Data1].NA_1, [t_Perioperative Data1].Q2, [t_Perioperative Data1].NA_2, [t_Perioperative Data1].Q3, [t_Perioperative Data1].NA_3, [t_Perioperative Data1].Q4, [t_Perioperative Data1].NA_4, [t_Perioperative Data1].Q4a, [t_Perioperative Data1].NA_4a, [t_Perioperative Data1].Q5, [t_Perioperative Data1].NA_5, [t_Perioperative Data1].Q5a, [t_Perioperative Data1].NA_5a, [t_Perioperative Data1].Q6, [t_Perioperative Data1].NA_6, [t_Perioperative Data1].Interoperative, [t_Perioperative Data1].Q8, [t_Perioperative Data1].NA_8, [t_Perioperative Data1].Q9, [t_Perioperative Data1].NA_9, [t_Perioperative Data1].Q10, [t_Perioperative Data1].NA_10, [t_Perioperative Data1].Q11, [t_Perioperative Data1].NA_11, [t_Perioperative Data1].Q12, [t_Perioperative Data1].NA_12, [t_Perioperative Data1].Q13, [t_Perioperative Data1].NA_13, [t_Perioperative Data1].Q13a, [t_Perioperative Data1].NA13a, [t_Perioperative Data1].Q14, [t_Perioperative Data1].NA_14, [t_Perioperative Data1].Postoperative, [t_Perioperative Data1].Q15, [t_Perioperative Data1].NA_15, [t_Perioperative Data1].Q16, [t_Perioperative Data1].NA_16, [t_Perioperative Data1].Comments, [t_Perioperative Data1].Q17, [t_Perioperative Data1].Q18, [t_Perioperative Data1].Q19, [t_Perioperative Data1].Q20, [t_Perioperative Data1].NA_20, [t_Perioperative Data1].Q21, [t_Perioperative Data1].Q22, [t_Perioperative Data1].AbxCom, [t_Perioperative Data1].HebCom, [t_Perioperative Data1].EquipCom, [t_Perioperative Data1].RMCom, t_Visit.vid
FROM t_Visit INNER JOIN (t_Physician INNER JOIN [t_Perioperative Data1] ON t_Physician.Physician=[t_Perioperative Data1].Physician) ON (t_Physician.DrID=t_Visit.Physician) AND (t_Visit.VisitDate=[t_Perioperative Data1].Date) AND (t_Visit.DOB=[t_Perioperative Data1].DOB)
WHERE ((([t_Perioperative Data1].DOB) Is Not Null)) And [t_Perioperative Data1].Physician like "J. Mc*";

Append TM
INSERT INTO [t_Temperature _Monitoring] ( [Anesthesia Start Time], [Anesthesia End Time], TQ1, TQ2, TQ3, TQ4, TQ5, TQ6, TQ7, TQ8, TQ9, TQ10, TQ11, TQ12, TQ13, TQ14, [Comment(s)], TmpCom, vil )
SELECT [t_Temperature _Monitoring1].[Anesthesia Start Time], [t_Temperature _Monitoring1].[Anesthesia End Time], [t_Temperature _Monitoring1].Q1, [t_Temperature _Monitoring1].Q2, [t_Temperature _Monitoring1].Q3, [t_Temperature _Monitoring1].Q4, [t_Temperature _Monitoring1].Q5, [t_Temperature _Monitoring1].Q6, [t_Temperature _Monitoring1].Q7, [t_Temperature _Monitoring1].Q8, [t_Temperature _Monitoring1].Q9, [t_Temperature _Monitoring1].Q10, [t_Temperature _Monitoring1].Q11, [t_Temperature _Monitoring1].Q12, [t_Temperature _Monitoring1].Q13, [t_Temperature _Monitoring1].Q14, [t_Temperature _Monitoring1].[Comment(s)], [t_Temperature _Monitoring1].TmpCom, t_Visit.vid
FROM t_Visit INNER JOIN ([t_Temperature _Monitoring1] INNER JOIN t_Physician ON [t_Temperature _Monitoring1].Physician=t_Physician.Physician) ON (t_Physician.DrID=t_Visit.Physician) AND (t_Visit.DOB=[t_Temperature _Monitoring1].DOB) AND (t_Visit.VisitDate=[t_Temperature _Monitoring1].Date)
WHERE ((([t_Temperature _Monitoring1].DOB) Is Not Null)) And [t_Temperature _Monitoring1].Physician Like "J. McG*";

Append DVT
INSERT INTO t_DVT_Data ( BMI, [History of VTE Prophylaxsis in the Past], [ASA Class], [Cardiovascular History], [Ca Channel or Beta Blocker Medication], [Antihypertensive Medication], [Position], Position_2, Position_3, [VTE Prophylaxsis Used], [VTE Prophylaxsis Used_2], [VTE Prophylaxsis Used_3], [VTE Prophylaxsis Used_4], [VTE Prophylaxsis Used_5], [VTE Prophylaxsis Used_6], [VTE Prophylaxsis Used_7], [Therapy In Place Until LII], [Anesthesia Type], [Location of Surgery], [DVT Occurred], [DVT Tx], [History of Breast Cancer], [DVT_Comment(s)], vil )
SELECT t_DVT_Data1.BMI, t_DVT_Data1.[History of VTE Prophylaxsis in the Past], t_DVT_Data1.[ASA Class], t_DVT_Data1.[Cardiovascular History], t_DVT_Data1.[Ca Channel or Beta Blocker Medication], t_DVT_Data1.[Antihypertensive Medication], t_DVT_Data1.Position, t_DVT_Data1.Position_2, t_DVT_Data1.Position_3, t_DVT_Data1.[VTE Prophylaxsis Used], t_DVT_Data1.[VTE Prophylaxsis Used_2], t_DVT_Data1.[VTE Prophylaxsis Used_3], t_DVT_Data1.[VTE Prophylaxsis Used_4], t_DVT_Data1.[VTE Prophylaxsis Used_5], t_DVT_Data1.[VTE Prophylaxsis Used_6], t_DVT_Data1.[VTE Prophylaxsis Used_7], t_DVT_Data1.[Therapy In Place Until LII], t_DVT_Data1.[Anesthesia Type], t_DVT_Data1.[Location of Surgery], t_DVT_Data1.[DVT Occurred], t_DVT_Data1.[DVT Tx], t_DVT_Data1.[History of Breast Cancer], t_DVT_Data1.[DVT_Comment(s)], t_Visit.vid
FROM t_Visit INNER JOIN (t_Physician INNER JOIN t_DVT_Data1 ON t_Physician.Physician=t_DVT_Data1.Physician) ON (t_Visit.VisitDate=t_DVT_Data1.Date) AND (t_Visit.DOB=t_DVT_Data1.DOB) AND (t_Physician.DrID=t_Visit.Physician)
WHERE (((t_DVT_Data1.DOB) Is Not Null)) And t_DVT_Data1.Physcian Like “J. McG*”;
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:15
Joined
Oct 29, 2018
Messages
21,358
Hi. Try converting that into a SELECT query first to make sure you are getting the records you want to append? Once you fix the SELECT query, you can then change it into an APPEND query.
 

danbl

Registered User.
Local time
Today, 14:45
Joined
Mar 27, 2006
Messages
262
I am a bit confused .... the piece that is not working is the And statement in the where condition for physician name. Physician name is a short text field and DrId is an auto number field
Previously with the date condition it worked.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:15
Joined
Oct 29, 2018
Messages
21,358
I am a bit confused .... the piece that is not working is the And statement in the where condition for physician name. Physician name is a short text field and DrId is an auto number field
Previously with the date condition it worked.
Hi. If you use a SELECT query, it would be easier to troubleshoot why it's not working because you'll get either the result from Access or an error message.
 

danbl

Registered User.
Local time
Today, 14:45
Joined
Mar 27, 2006
Messages
262
I have taken the append query and changed to Select and still get no records. I went to the visit query changed it to select and copied the physician parameter, which worked, into the others and get no results
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:15
Joined
Oct 29, 2018
Messages
21,358
I have taken the append query and changed to Select and still get no records. I went to the visit query changed it to select and copied the physician parameter, which worked, into the others and get no results
Sorry, since we can't see your data, you will have to keep troubleshooting until you can get the SELECT query to work. Only after you get the SELECT query to work can you guarantee the APPEND query will work too. If you can post a sample copy of your db with test data, maybe we can help you troubleshoot it.
 

danbl

Registered User.
Local time
Today, 14:45
Joined
Mar 27, 2006
Messages
262
Good Day,

Have you had any luck with the zip files I sent??? :unsure: :unsure:
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:15
Joined
Oct 29, 2018
Messages
21,358
Good Day,

Have you had any luck with the zip files I sent??? :unsure: :unsure:
Hi. Sorry, not yet. I just saw them - I haven't downloaded them yet.
 

Users who are viewing this thread

Top Bottom