Transform csv file to match access table for import

[jdraw]

Should this last line tblMedicalTreatment.MedicalTreatment;
be tblMedicalTreatment.MedicalTreatmentId

 

[standenman]

I tried that - still fails. Weird.

 

[cheekybuddha]

You have a typo - you have been staring at it too long!

FROM tblMaster INNER JOIN tbMedicalTreatment ON tblMaster.[Medication name (on Bottle)]= tblMedicalTreatment.MedicalTreatment;

MS highlights " tblMedicalTreatment" in the SELECT DISTINCT line when it complains of "syntax error in JOIN operation".

Double check this line:

FROM tblMaster INNER JOIN tbMedicalTreatment ON tblMaster.[Medication name (on Bottle)]= tblMedicalTreatment.MedicalTreatment;
                            ^
                            |

think you missed an 'l'

 

[standenman]

You have a typo - you have been staring at it too long!


Double check this line:

FROM tblMaster INNER JOIN tbMedicalTreatment ON tblMaster.[Medication name (on Bottle)]= tblMedicalTreatment.MedicalTreatment;
                            ^
                            |

think you missed an 'l'

Wow. Thanks so much for your keen eyes! Yes, that was the problem!

 

[standenman]

Actually, no. That was what made the code fail: now it runs but appends no records. An old friend used to have a phrase "like a monkey with a beer tab" which I think applies to me - I keep cutting my fingers because I really don't understand how these queries work. I have simply copied from other code.

But all the queries appear to work save the last that is intended to populate tblMeds. Rather than populating my tblMeds with 5 medications, no records are returned. Here is the query:

INSERT INTO tblMeds ( MedicationName, MedicalTreatmentID )
SELECT DISTINCT tblMaster.[Medication Name (on Bottle)], tblMedicalTreatment.MedicalTreatmentID
FROM tblMaster INNER JOIN tblMedicalTreatment ON tblMaster.[Medication name (on Bottle)]= tblMedicalTreatment.MedicalTreatment;

I am at a loss to understand why and my long monkey fingers are bleeding on the keyboard!

 

[The_Doc_Man]

It is possible that I don't understand all of your data sources, but as a side comment, special characters in a field name are often troublesome. Might not lead to monkey blood, but are a good source of headaches.

If your question is why that last INSERT query doesn't append anything, you need to recognize that the INSERT INTO is actually a layered query for which the layer isn't completely obvious. You can take out the entire INSERT INTO clause and start a query with the SELECT DISTINCT clause (and the same FROM clause) to see what would be selected (and thus what would be inserted). If you can get the SELECT to actually select something, you can then put the INSERT INTO clause back into the query ahead of the SELECT clause and it should work.