Solved Not All Data Appended

[danbl]

I am NOT very knowledgeable on SQL! I have had a great deal of help with the Append query below, and there are a total of 5 append queries, included below. The issue is not all the data came over in the append. This was due to a miss spelling in the physician origin table. The correction has been made.

As I am not at all proficient in SQL can someone assist me in how to modify these queries to specify just the one physician either by ID or his name??

Thank you in advance …

Append 1
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.Date>#12/31/2012#
ORDER BY t_Advanced_Directive1.Date;

Append 2
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.DOB=t_Advanced_Directive1.DOB) AND (t_Visit.VisitDate=t_Advanced_Directive1.Date)) ON (t_Physician.Physician=t_Advanced_Directive1.Physician) AND (t_Physician.DrID=t_Visit.Physician)
WHERE (((t_Advanced_Directive1.DOB) Is Not Null)) And t_Advanced_Directive1.Date>#12/31/2012#;

Append 3
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_Visit.DOB=[t_Perioperative Data1].DOB) AND (t_Visit.VisitDate=[t_Perioperative Data1].Date) AND (t_Physician.DrID=t_Visit.Physician)
WHERE ((([t_Perioperative Data1].DOB) Is Not Null)) And [t_Perioperative Data1].Date>#12/31/2012#;

Append 4
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_Visit.VisitDate=[t_Temperature _Monitoring1].Date) AND (t_Visit.DOB=[t_Temperature _Monitoring1].DOB) AND (t_Physician.DrID=t_Visit.Physician)
WHERE ((([t_Temperature _Monitoring1].DOB) Is Not Null)) And [t_Temperature _Monitoring1].Date>#12/31/2012#;

Append 5
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.Date>#12/31/2012#;

 

[Guus2005]

A few things come to mind:
Your database design needs a little work. It is not normalised. The fields: TQ1, TQ2, TQ3, TQ4, TQ5, TQ6 ,... is a repeating group.
You have 5 append queries which are used to fill 5 tables. Why not use these queries as tables?

And i am not sure what you are asking,

how to modify these queries to specify just the one physician either by ID or his name?

There is a table t_Physician. And i assume that t_Physician.Physician is a Physician ID? (He asked hopefully). Because there is a join mentioned on this value. Usually you don't join on a name but rather on an ID.

For a better answer, add in a sample database. Filled with fake data. Two or three records in a table is more than enough.

HTH

 

[plog]

APPEND queries are generally hacks used by people who don't fully understand how databases work. So, I have to ask, why are you using an APPEND instead of just a SELECT? Why must data be moved from one table to another?

 

[The_Doc_Man]

I'll agree with both Guus2005 and plog that you have structural issues.

However, the direct answer to your question is that if you want to only see something for one physician, you put something in the WHERE clause to limit what is retrieved. From your 3rd query:

WHERE ( [t_Perioperative Data1].DOB Is Not Null ) And ( [t_Perioperative Data1].Date>#12/31/2012# ) AND ( tPhysician.Physican = 'somename' ) ;

You would of course have to supply the information of which physician you wanted. If you are going by name, you would use a quoted string around the name. But I don't know whether that is actually a physician number, in which case you would use an unquoted number to select a physician by his/her ID number. That depends on what you have for a DB.

 

[danbl]

Thank you to all …. the initial database was poorly designed on my part as not all that has been incorporated was initially thought about or requested
The append queries were suggested a long time ago by someone on this forum (cant remember who but that is not important) after the initial database was looked at and compared to a revised database. The goal was to have a new revised and up to date database to use going forward. I must say the appends did exactly as they were intended except for the error caused by the miss-spelling issue. I believe The_Doc_Man's code will bee my answer.
The physician table has both ID and name, does it make any difference which value I use??

 

[danbl]

The suggestion from The_DocMan did not work Not by name or ID

 

[danbl]

What is the max file size??

 

[The_Doc_Man]

If the ID is numeric, you don't need quotes around it. If the choice you are making is by name, you DO need the single-quotes.

When you say "did not work" there are a myriad of ills possible under that general description. If you go to a doctor and say "I feel sick" you KNOW s/he will ask for more specific symptoms. Well, we are Access doctors here and need to know symptoms.

I could easily imagine one of several results, so please specify... A. No records returned, B. Way too many records returned or C. Some error was returned. For future reference, when you have issues like this, remember that we need symptoms to help us orient on the problem at hand.

 

[danbl]

When I said it did not work, I meant that no data for the physician in question came over either by using his name or ID#.
However I deleted all records, removed the date and individual physician criteria, re-ran the append queries, and all the data came over???

 

[The_Doc_Man]

Hmmm.... by any chance did you previously have error messaging disabled? It MIGHT have been trying to tell you something about a conflict with a unique key but with notifications turned off, you wouldn't have been notified. So when you killed the content and started over, you got rid of the conflicts. That would explain it, perhaps. Otherwise, you've got me scratching my head.

 

[danbl]

I did not receive any error messaging ………………… although I am fare fare from being very knowledgable about this it is puzzeling to me also.

Why did it work after I changed the parameters??? I appreciate your continued replies .

 

[The_Doc_Man]

OK, you've got INNER JOIN in several of those queries. If it happens that some elements on one side of an INNER JOIN are not matched by elements on the other side then you will not get the unmatched data - even though there is no NULL involved. Just the absence of data is enough to limit the results involving INNER JOIN logic. You would have to look at the cases you missed to see if they depended on something that wasn't there. But you said you already have it working after a reset of the table contents. So you can't test it any more, but that data reload might have fixed the problem.

 

[danbl]

Doc_Man thank you for continuing to reply.
I am learning a bit about this and believe that the mis-spelling may have caused the issue. After correction and starting over, all the data came over to the new database.
Thanks again for your help!!!

 

[The_Doc_Man]

An errant spelling error certainly WOULD cause an INNER JOIN to fail to join some records, so I would concur with your idea about why it failed.

Glad to have helped.