Import routine that only grabs new records 2

[jsimes]

Hello,

I ave a bit of a dilemma. I ahve a client that wants PeachTree data in an Access database. I had the client export the appropriate data from PeachTree (Customers and Inventory). It is quite a simple list of items the customer export has only the customer ID and the Customer Name and the inventory export only has the Item ID and the Item Description. The rub is that the client can't seem to export only new data (ie. new entrys since the last export). The dump the whole shebang. So when I import it just takes the whole Excel file and appends it to the existing table. So I have duplicats all over the place (I had to create my own primary key in the table I import into because the Customer ID is not unique). So essentially I need to take the imported data and slog through it and find only the new data and append that to the tables(customer and inventory).

Is there a quick and easy way to do this or is the import/append gonna be a slow process (import into temp table and run a comparison query be fore the append)?

Thanks for your help!!

Joe

 

[JeremyM]

The fastest way i can think of is to sort both the table (always keep sorted) and write your own insert/search command (ie. quick search). You can write the code in the module part to loop through looking for duplicates. It will be quicker than Access Queries but will still be rather tedious.

 

[JeremyM]

The fastest way i can think of is to sort both the table (always keep sorted) and write your own insert/search command (ie. quick search). You can write the code in the module part to loop through looking for duplicates. It will be quicker than Access Queries but will still be rather tedious.

Jeremy

 

[DreewToo]

Hi Joe,

Your message didn't mention the presence of a primary key in both tables, but I'm hoping this was an oversite. If it was, you could do an:

APPEND INTO tblDESTINATION SELECT * FROM [table to import] WHERE tblIMPORT.ID <> tblDESTINATION.ID.
(my sql append syntax is a little rusty, but i think it's something like this. I'm pretty sure you need to specify the column names too. The important part is the WHERE portion of the code)

If there is no primary key similar in both tables, I suspect there is a combination of fields, ie. CustomerID &amp; OrderID &amp; Date (or something like that) where you can concatenate the fields into creating an index which would make the record unique. With this combined field, the same technique would work.

HTH,
Drew

 

[jsimes]

Hey thanks to all who have replied!!

Here is my solution to the dilemma (yucky code to follow):


First I import the Spreadsheet to a TEMP table, then there is an INSERT QUERY that compares the real table (customer_export) to a QUERY that finds the different records between the two tables (tables: 'customer_export', 'TEMP'; Comparison QUERY: 'Records in TEMP not in customer_export')


INSERT INTO customer_export ( Customer, [CustomerID] )
SELECT [Records in TEMP not in customer_export].[Customer], [Records in TEMP not in customer_export].[Customer ID]
FROM [Records in TEMP not in customer_export];


Here is the 'Records in TEMP not in customer_export' QUERY:


SELECT TEMP.Customer, TEMP.[Customer ID]
FROM TEMP LEFT JOIN customer_export ON TEMP.[Customer ID] = customer_export.[Customer ID]
WHERE (((customer_export.[Customer ID]) Is Null));


I really don't know if this is the most effecient code there is but it does the trick. (Don't you love my more than obvious Names for my Queries!!)

Thanks again,

Joe