Importing new data

T

tungstentim

I do a yearly maintenance on several databases. I import new data and compare
to the old data. I manually check a box to select whether to import new data
or leave old data alone. The concept in my head seems simple but I can't come
up with a way to do this progromatically.

Here is the issue. I want to "append" new records from "NewDataTable" to
"OriginalDataTable" only if the "ParcelId" field in the "NewDataTable is not
the same as any "ParcelId" field in the OriginalDataTable". This process is
easy enough. But here is where I get fuzzy. I want to update the records in
the "OriginalDataTable" with records from "NewDataTable" if the info in
"ParcelId" field in both tables is equal but "OwnerName" field is different.
I.E. New Owner info, but same Parcel Info. If "OwnerName" and "ParcelId" is
same in both tables there is no need to update th record. Thanks in advance.
 
T

Todos Menos [MSFT]

get a real ETL tool and lose MS Access

Access isn't an ETL tool, kid
 
N

njack217

you need to make a find duplicates query, change it to a delete query,
and then make an append query to update the remaining data. the
wizards will help you do most of this.
 
S

SteveD

I would be using an Update query. Of Course -- try this in a small test
instance first to get it correct.
The update query would link the ParcelID in each table, The Criteria would
be where the original.owner <> new.owner then update the specific fields in
the original table with the new data.

My example is two tables (Original and Newtable) where the structure is the
same, but I change a couple of owners. the query will update on the changed
owners.

Here's the SQl from my sample query:
UPDATE NewTable INNER JOIN Original ON NewTable.SerialNbr =
Original.SerialNbr SET Original.RPA_OWNER_NAME = [NewTable].[RPA_Owner_Name]
WHERE (((Original.RPA_OWNER_NAME)<>[NewTable].[RPA_Owner_Name]));

Hope that helps you,
SteveD
 
T

Tony Toews [MVP]

get a real ETL tool and lose MS Access

Access isn't an ETL tool, kid
Click to expand...



Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
T

Todos Menos [MSFT]

Tony

if you think that Access is an ETL tool-- then please tell me how yuo
can use access to connect to SQL Server and pull data once a night.

In SQL Server, I can do this EASILY without writing _any_ code
 
Top