Transform csv file to match access table for import

[CJ_London]

you have 3 records in your spreadsheet one with a name and two blanks - so distinct provides one name and one blank, you need to exclude the blank.

I'm going to drop out at this point, simply because it is not the way I would do it (I've already suggested what needs to be done and the original format was much easier from a data import perspective), the input format has changed and I can see other issues down the line because of those blanks.

Good luck with your project, but it is going down a path I don't want to follow

 

[standenman]

I did not think I was ignoring your approach save the starting out with transferspreadsheet. Thank you for all the help you have given me. I appreciate it.

OK so the slow slog begins. I am seeking to enter a group of client medications. Here is the table organization:
View attachment 102192

In creating a sequence of append queries I am starting with creating a record in tblClient with name and SSN. i have created a "mastertable" via code in this form:

Private Sub Command0_Click()
Dim strPath As String

strPath = "C:\Users\StanleyDenman\Downloads\ClientMedicationsStanleyDenman.xlsx"
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
TableName:="tblMaster", _
FileName:=strPath, _
HasFieldNames:=True

End Sub

Now my goal is to use the mastertable for the append queries. Here is the mastertable ith dummy data:
View attachment 102193
Here is my first append query:

INSERT INTO tblClient ( Name, SSN )
SELECT DISTINCT Name, [Social Security Number]
FROM tblMaster;

When I run it the append query puts 2 records in tblClient, ond of which is blank - not what I expected. I expected just one. The append query seems to think we need a blank record - I don't see where that is coming from.

OK solved it. Changed append query to:

INSERT INTO tblClient ( Name, SSN )
SELECT DISTINCT Name, [Social Security Number]
FROM tblMaster
WHERE Name IS NOT NULL;

 

[standenman]

OK. I am about out of gas on this one. My serious of append queries which basically take a flat csv file and incorporate the date into my relational DB seems ALMOST there! Access, however, complains at the last query, saying I have a syntax error in the join operation. First pic is my table organization. The append queries start at tblClient and work until the last seeking to populate tblMeds. Here is the MasterTable ith dummy data.

here are the append queries, in order of execution:

QUERY1:
INSERT INTO tblClient ( Name, SSN )
SELECT DISTINCT Name, [Social Security Number]
FROM tblMaster
WHERE Name IS NOT NULL;

QUERY2:
INSERT INTO tblImpairments ( Impairment, ClientID )
SELECT DISTINCT [What for?], tblClient.ClientID
FROM tblMaster INNER JOIN tblClient ON tblMaster.name= tblClient.name;

QUERY3:
INSERT INTO tblMedicalTreatment ( MedicalTreatment, ImpairmentID )
SELECT DISTINCT tblMaster.Treatment, tblimpairments.ImpairmentID
FROM tblMaster INNER JOIN tblimpairments ON tblMaster.[What for?]= tblimpairments.Impairment;


QUERY4: (the one that fails):
INSERT INTO tblMeds ( MedicationName, MedicalTreatmentID )
SELECT DISTINCT tblMaster.[Medication Name (on Bottle)], tblMedicalTreatment.MedicalTreatmentID
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".

Thank you in advance to all.

 

[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.