Import from CSV

[Stan Denman]

Years ago I used an access book that provided for a very complicated contact management schema that has been biting me in the backside ever since. The main contacts I need to manage are clients, but there are also various 3rd parties like doctors. The address management was a many to many and like I said, too complex, and too unwieldy. In my MS access DB I had the bright idea of "subclassing" contacts.

I have a Contacts Table and a Client Table. Contacts is everyone, client is, well, clients. Because the address management "broke down" I shifted to simply housing address and other data for clients in the client table.

I had a hard drive crash this week and lost many MS DB client records. I use the MS DB to populate Acrobat forms for client signature. Those signed client document are stored on the Adobe cloud. You can click on a file in the above dashboard and get "download form field data" which gives you a csv file of this form's data (name, DOB, Address, etc) which can help me to repopulate my MS database. I have managed to merge these separate csv files and this is the source of data I want to use. But my client table has a vestige of the folly of following a complete MS Access book!

In my Clients table I have a foreign field, ContactID, as primary. That field, ContactID is autonumber, primary in table Contacts. So when I import my data from the csv file I have


1. Client a record in table Contacts
2. Create a record in table Clients with the ContactID just created in table Contacts
3. Add the rest of the data in the csv file to that created record in table Clients.

Any help would be most appreciated.

[CJ_London]

What code are you using and what help are you asking for?

I suspect you want to find the id of the new contact record and if you are are you are using sql to populate the contact table you should be able to use @@identity

[Gicu]

Some more info along the same lines, showing your attempted code would help getting more pertinent answers:
https://stackoverflow.com/questions/...g-foreign-keys
https://stackoverflow.com/questions/...y-relationship
Cheers,

[Stan Denman]

Thanks for your responses. I don't get it sorry. Have no idea what @@identity is. MS Access much too complicated for me. Wish I had started with something else years ago! I'll just undo the complicated "subclassing".

[Gicu]

It is not that complicated; you don't say how you import the csv (built in Import button on the ribbon vs custom code), but basically the way this usually works is you import the entire csv into a temporary Access table with all the fields that exist in the csv file. Then you us an append query to add the required fields to the Contacts table. And finally you use a second append query to add the rest of the fields from the temporary table to the Clients table using Dmax("ContactID","Contacts") as the foreign key for the newly added record in the Contacts. Last step is to empty the temporary table and import a new csv....
Cheers,

[Stan Denman]

OK. Thanks.

[madpiet]

Depends on how your PDFs are structured. If they have tables, I'd use Excel to import the data, because it can read PDF files... well, PowerQuery can.

[Stan Denman]

So I have added records to ContactID from the temporary table made from the csv file. Now I want to add records to the client table. I want add the data based upon the criteria of matching the last name. So I would create a record in table Client for Joe Blow with the right SSN and the right ClientID. Not sure how to do this. This is my append query. I don't understand how to get the right ClientID in there


.

[Gicu]

I think you mean the "right ContactID". If you process this one csv\client at the time (and the ContactID in Contacts is set up as an incrementing autonumber) you would simply add a calculated field to your append query: ContactID:Dmax("ContactID","Contacts") (as the mas will be the one you just added) and append that the foreign key in Clients.
Cheers,

[Stan Denman]

I have already added 10 records to the Contacts table. ContactID was created automatically. The name of the person is also in table Contact. Now I simply want to create records in the client table with data from the temporary table putting in the right contact id based upon matching the last name from contact table and the temporary table. This is what chatGPT thinks I should do:

INSERT INTO Clients (ContactID, SSN, ClientMailingAddr, City, State, Zip)SELECT C.ContactID, T.ClientSSN, T.ClientMailingAddr1, T.ClientMailingAddrCity, T.ClientMailingAddrState, T.ClientAddrZip
FROM TempData AS T
INNER JOIN Contacts AS C ON T.LastName = C.LastName;

[Gicu]

Yes, if you have multiple records you want to process you can join on LastName; but in case you have duplicate last names you could also add a second join on another field (FirstName or DateOdBirth). And I would suggest you change the join type to (left) OUTTER JOIN where the LastName in Clients Is Null (look at how the Access wizard creates a "unmatched query" between Contacts and Clients on LastName) to avoid creating duplicates if you run this query multiple times.

If you can upload a sample file with just the three tables (tempData,COntacts,Clients) and the queries you already have it would help a lot.
Cheers,

[Stan Denman]

Thanks for your offer, but my data includes SSNs. I have this code and when I try to run it it says it will append 15 records. Pretty amazing when TempData table only has 10 records! I just hate Access:

INSERT INTO tblClients ( ContactID, SSN, MailingAddr1, City, State, Zip )
SELECT tlbCont.ContactID, TempData.ClientSSN, TempData.ClientMailingAddr1, TempData.ClientMailingAddrCity, TempData.ClientMailingAddrState, TempData.ClientMailingAddrZip
FROM TempData INNER JOIN tblCont AS tlbCont ON TempData.ClientLastName = tlbCont.LastName;

[madpiet]

SSNs in Access is an epic fail. Zero data security. Hard pass on that one.

[Gicu]

That is because you probably have duplicates in the tblCont for the LastNames, hence my earlier suggestions to add another field to the join to avoid ambiguity and make the join an outer join to avoid adding the same records multiple times.
Uploading a sample database with "dummy" records will help us help you....
Cheers,

[Gicu]

You don't upload the real data, just a couple of dummy records in each table with fake SSN, names, addresses, etc. to allow us to look at and write the queries...