Adds and Changes

[ecrrolson]

I'm new to this forum, and to Access...Please have patience with me!!!
I have been tasked with setting up a DB in Access for Sales Lead tracking and Paying Commission.

We recieve an extract (csv) weekly from another DB, which has Sales Lead information, ie. Company, Name, last Call, Sales person ID, etc...
All I want to do is import this data weekly into my master DB, and Add the record if it does not exist,
Change the record if it has changed, or
Ignore the record if it is the same...

This should be very simple, but I can't seem to accomplish it.

Any ideas or input would be greatly appreciated.

Thanks, Erik

 

[Fornatian]

The first thing you need to establish is how you are going to identify which records are the same? Do they have a unique ID no? Do more than one field create a unique reference for the record?

Once you are confident that the unique record identifiers are correctly determined you are in a position to indentify and updating the records.

Firstly create a new temporary table that will hold the data from the csv file.

You then need an update query with criteria to update the fields and an append query to add new records which you don't currently hold.(I believe you can do this with one update query but I am not super-confident)

At the end of the procedure you can delete the temporary table until the next time and recreate it to save db space.

Before making any kind of updates or additions which you are unsure about back up

Need more info, pls come back.

Ian

 

[ecrrolson]

So there is on utility that will analyze the data as it is loading, and do the corresponding add or change?

 

[Fornatian]

Give an example of one line of the data of so we know what data you are dealing with.

In answer to your question - a PC won't know how to id which records relate to which records unless you develop some protocol for identifying related records. The PC knows nothing unless you tell it so.

 

[ecrrolson]

In other applications I have used there are utilities which will compare each value of the new data, to the existing data and add or change depending on what is found...
In the initial Load the Hotel Number may not be input yet, in the next extract based on Date of last update, the Hotel is in the record but so may have the conatact changed to Sally Jones...
I need all record to update, if they have changed. Record No. is the Unique Key.
Thanks, Erik

"Prefix","FirstName","LastName","Title","Phone1","Phone2","Fax","Email","Record No.","Company","Address1","Address 2","Address 3","City","State","Zip","Country","Web Address","SIC Code","# Emp.","future 1","Ultimate Duns","Site Duns","List Source","future 2","Contacted Date","Entered","Last Updated","Edited By","Marketing Code","Campaign","Initial Status","Lead Status","Hotel #"

"","Michael","Smith","","765-555-5555","","847-555-5555","mikesmith@city.com","21275","ABC Co.","123 N. Main","","","Mt. Prospect","IL","66678","","","0","0","0","","","","","05/30/2002","5/29/2002","6/11/2002","Mike Jones","CW19","Chicago Hoffman","","D LEAD","0001"

 

[Pat Hartman]

Look up the following article in the Microsoft knowledge base.

Q127977 - ACC Updating and Appending Records in One Update Query

 

[ecrrolson]

Thanks Pat Hartman, and Fornation for replying...
The MS Knowledge base example (Q127977), that Pat sent me, was right on the money. I have used the example and have the update query working great!!!
Thanks, again!!
Erik