Compare Two tables And Update Changes

[james_halliwell]

Hi All,

Background,
i import data into a Landing table, this will always contain d days woth of data, i then run an apend query into another table were the records are stored up to 20 days,

the problem i face is some of the record data may change for eample a field called ShippedQty may be 0 in a monday but on tuesday it may say 5

Import on monday
Product ShippedQty Date
123____ 0 ________01/01/2013

Import on Tuesday
Product ShippedQty Date
123____ 1 ________01/01/2013

I need to look at any changes and updatein the master table, is there a way to do this

(I cannot change the import as its the only way i can get the data)

many thanks

 

[jdraw]

Forget the import for a moment.
What exactly are you trying to do? What do you need from the data? Do you run queries? Do you interact with Finance? Do you print reports?

Please give us some context about the overall issue.
Please help us help you.

What is a landing table?

 

[james_halliwell]

Hi and sorry will give you some more background

I get a text file from our system everyday which contains 2 days worth of orders,
The landing table is a table where I import the records to

I only keep orders for 20 days and then they are deleted ( I only need monitor 20 days)

What needs to happen is the import only adds new records at don't exist on the master table and update and changes if there is a difference from the landing table to the master table

Many thanks hope this makes sence

 

[gemma-the-husky]

you need a way to match the new data with the old data - so maybe match on product and date.

then 1 query to append records that aren't there.
another query to update record that are there.
and maybe another tpo identify records that disappeared (in case the crafty blighters changed some dates or deleted some rows)

It only becomes an issue if you cannot uniquely determine the access row from the spreadsheet data.