Program Design advice

[purceld2]

I need to write a program which check to see if certain dates in a table have changed basically monitor date changes on a daily basis.

The data is retrieve from another database. I plan to created to create a new table which will hold the dates and on a daily basis check these dates with the dates retrieve from the remote to see if any of the dates have change and report.

My question is what is the most efficient way to do this the volume of records is approx 6000.

I could sort both tables on primary key and walk though both tables crosschecking dates

Or

use a select query to lookup individual items checking dates

And adding issue is that there could be new items which will need to be added to date checking table

Any suggestion will be appreciated

Thank You

Des

 

[DCrake]

firstly you would need to import the table into an empty one.

Then on a daily basis import the data into an empty dump table
Then run an append query using an unmatched query. This will get you the new records in the main table without duplication.

Then you can have a query between you main table and the dump table joining on the appropriate matching fields.

Include an extra column in your query

Different: IIf(DateDiff("d","DateInMainTable","DateInDumpTable")<>0,True,False)

Essentially it compares the old date with the new date if it is different it falgs it as true.

Finally in your condition row enter True

This will then list only those records that have a difference in the dates.

This is all air code and untested but I hope you understand the logic.

David

 

[purceld2]

Thanks DClarke for you reply one query

You say "Then run an append query using an unmatched query"
Is this done with one SQL statement or the unmatched query followed by an append query using the the resulting data

Could you give me an example how the SQL command would look

Kind Regards

Des

 

[DCrake]

If you create an unmatched query initially then change its action form a select query to an append query and point the data to your main table. The select element of the query will still be preserved but instead of just reteiving the data it also appends it to your main table.

David