Question Export to, then import back from Excel

[mountainview]

I have a customer database with consists of tables for:
Customer
Contacts
Notes
I have created all the link and the one to many relationships.

I have a field in the contacts table called key words, which i populate with particular products that the individual may be interested in e.g. cars boats etc.

I have a query that looks for individual words when I want to, for example, do a mailshot with new literature for cars. It provides:

Customer and mailing address
Individual employee name salutation etc.

This is exported to Excel with our office then use to produce the mailshot, they also add as additional columns

Date sent
Literature sent

In the Notes table, I record activity with each individual, such as date called, what happened etc.

I would like to be able to take the "date sent" and Literature sent date from the updated Excel sheet, and "import" it to my Notes table for each individual that was sent the literature.

So, I'm exporting data from multiple tables, which all works fine, but I want to add to the Notes table once the mailing is done.

Not sure where to start with this? Any help would be appreciated.

Thanks Paul

 

[boerbende]

link your excel to access as external datasource or import it as table.
then use an update query where you join identical fields from excel and the note table and update the date field in the note table with the data in excel

 

[mountainview]

link your excel to access as external datasource or import it as table.
then use an update query where you join identical fields from excel and the note table and update the date field in the note table with the data in excel

I've looked up the update query function, but it says it can't be used to create new records? is this correct. Effectively the records I'm wanting to bring back in the the notes field are new.

I used to use Lotus approach, and with that I could do a conditional import of a new record, by say exporting the contacts ID, and then using that as the import link? Is there a method in access to do the same please?

 

[boerbende]

You were talking about an export and an import of the notes, so I assumed this was an update.
For new queries you have to use the APPEND query. You can use the query builder in Access, this one is pretty straight forward.
Create a new query, select the linked table (your excel) , go to Design and select APPEND

 

[mountainview]

I've simplified the structure a bit, but I'm still missing something here :-(

I have my "contacts" table from which I export the contacts ID
I have my "activity" table which is a one to many relationship with contacts (contacts 1, activity many)

What I'm trying to do is import in to the "activity" table, based on the ID field in the "contacts" table.

So, I generate a number of contacts (ID) and export them to a spreadsheet, I then edit the spreadsheet with additional information that I want to appear in the "activity" table.

How do I import the data conditionally based on the contacts ID?

Any help would be appreciated, there is probably something simple I'm missing here :-(

 

[boerbende]

If I understand it well, you are nearly there.
Assuming your activity has contactID as column:
- Link the excel as external table. Name for example ExcelTable
- Build a query:

INSERT INTO Activity
SELECT CustomerID, Activity from Exceltable

 

[mountainview]

Many thanks, I'll give that a try

 

[mountainview]

I've got so far, created the append query, but how do you set the criteria to make sure the ID link bring the correct data in to the right contact? Thanks