Excel Import & Append Unduplicated records

[Vittles]

I am used to MSAccess, but I am new at importing data on a regular basis and then appending it to multiple tables.

Each week we download a set of information into an excel spreadsheet from a larger system. I have a macro set up that limits the information in that spreadsheet to what we need to report on, as well as splitting the information into 2 worksheets based the one-many tables I have set up in Access (including adding the ID field from the main table to the linked many sided table). One worksheet is the main table information (general application data) and the 2nd worksheet is for the many sided table (various date fields).

I know I can import the main table information into an empty data-import table in Access and then use an append query to add any different records to the Main table.

Is there a good way to do a similar process for the many sided (12 date fields and a date imported field) table so that records are only added where any of the last imported record's information has changed?

We don’t want to update the current record, only add a record if info has changed. This will allow us to track date changes, etc.

Also, are there any on-line sources of information that may be helpful in learning how to automate the importing of this data? I will need to have someone else import both worksheets into the 2 tables on a weekly basis and would like to automate this process as much as possible.

Thank you for any ideas!

 

[psemianonymous]

Access replication.

Seriously, this is a very deep and tough problem, which Access has thoughtfully taken care of for you. Please read up on Access replication and you'll see that it does everything you're asking for as far as data merging goes, and as a big bonus, it solves a lot of problems you wouldn't otherwise see (but would have).

 

[apestaart]

I haven the same problem, but I can't find anything about "on Access replication" that matters.

Iam also looking for a VB routine to solve the problem.

I have in Excell Name Address City items en the 2 to 7 fields with Dates.

I need to import into two tables named "NAC" and "Dates"
The tables have an one to many relation.

Any help whould be appriciated.

Apestaart

 

[psemianonymous]

http://www.google.com/search?hl=en&q=access+replication

The first link is to "Access Replication FAQ", that should get you started.

 

[Vittles]

Since listing this post, I have designed macro/code to import each table via a data/import table created for each upload. It was fairly easy since I had already created a macro in Excel that split the information into 2 worksheets (within the same workbook) based on the Access table structure.

The key information I am still trying to figure out is how to add records where any of the most recently imported record information has changed? We don’t want to update the current record, only append a record if that info has changed.

I have worked with replicated databases, but am unsure how replicating this database will solve the above question as conflicted records actually replace information, right?

 

[Vittles]

I found a solution to my own question, but wanted to add it to my post so that others could have the information...

In order to append only changed records:

I first added the initial data records from the first downloaded excel file to the actual database tables. Then I created a select query using the 'Imported Data' table that is created each time I import the many-sided table's data. I added all of the fields from that table into the new ‘comparison query' field area(s). I also added the related Actual Data table only to be able to join them on an ID field (joined them so that all of the records showed up that were in the Actual Data table) - I didn't add any fields from that table into the field area(s).

For each of the Imported Data fields that I wanted the value compared to the 'Actual Data' many-sided table, I changed the field to be: Field1Name: Trim(Nz([Imported Data Table]![Field1Name]))

and then added a criteria line as <>Trim(Nz([Actual Data Table]![Field1Name]))

Each comparison field had this same formula/criteria added and the criteria for each was on a different line so that the query would see if Field1 in the Imported table matched Field1 in the Actual data table OR if Field2 matched, etc. I did not add the formula or criteria for those fields that were not being compared.

This query allowed me to see only those records that were different in the Imported Table versus the Actual Table.

That ‘comparison’ query was then used to create the append query used to append these changed records to the Actual Data table.

As an added measure I also based the form and reports that showed this data on a query that grouped by the ID field, so only those records that had multiple listings showed up on the ‘changed information only report’

I setup macros/coding to automate all importing functions.