Update /Import Specifications

[Guest]

Question

Hi

Perhaps one of you good people will be able to help me.
I import order details from our order system into an Access database table,
this is quite simply an extract from Crystal saved to Excel format and
imported into the relevent table.

My problem is the Order Number is unique and thus acts as the primary key
field - not allowing duplicates, hence only new orders are imported. This
works fine until an exsisting order details are edited in the order system -
the extract reflects the changes against the original order number (the
unique identifier) but the import is rejected as the unique reference quite
rightly does not allow duplicates - unfortunatly the updated details specific
to the order number are not imported.

I have considered importing into a new table (deleting all current records
on import)
then running an update query that matches the unique order reference and
updates all detail fields from the new table to the Order table.

This works but is there a better way of doing this directly from the import?

And most importantly because there are many orders how can I easily identify
which records have been updated from their original value????? I am only
interested in the records where values have actually changed - not the
records where the updated value remains the same as before. Is it possible
to chaNge the text colour where values have changed after updating ??????

Appreciate any help/suggestions on this matter

Kind Regards

GARMANI

 

[Jeff Boyce]

If you wish to only work with records that are "new" (new record, or altered
existing record), there are a couple approaches. You could have a way to
identify these within your original source of data, and only extract those.
Or you could import everything, then compare to everything you already have.
I'd recommend the former approach, if importing is your only option.

Is there a chance you can use an ODBC driver to connect directly to the
source, rather than use the somewhat circuitous route you now have?

If you did import all records, added those that are new, and updated the
rest, would it matter if the old and the "updated" record contained the same
values? You would know that what you had was the latest, and would not need
to concern yourself (or Access) with identfying the updated but unchanged
record (this is somewhat a semantic quibble -- if the record is truly
updated, it probably has been changed).

I use an "import" table as a staging area, then use queries to load my
"permanent" tables.

 

[Guest]

Hi Jeff

Many thanks for taking the trouble to respond - security issues prevent an
ODBC connection to the underlying source databases - hence the extract via
Crystal (which at the sime time does some calculations to convert orders to
pallet amounts etc)

I am struggling to identify a "timestamp" which identifies amended orders in
the order system otherwise I could import using this criteria.

Hence the problem remains of importing into an "import table" and reload the
"permanent" table using update and append queries.

This allows the current information to be available - where I am struggling
in the absence of any amendment times for the edited order, is identifying
which records are updated but not changed and which (more importantly ) are
updated and changed. Due to the number of records involved it is not as
apparent as one would think. Also information such as despatch dates and
delivery routes are allocated to orders within the table dependent on the
information details which are updated.

For example - Order 1234 despatch date 1/10/05 delivery to ABC is imported
and scheduled for delivery on a specific route - assume the order details are
edited and the despatch date changes to 1/11/05 yet the order number will
remain the same.

Consequently the suggested import will update against the unique order
number but the update needs to be flagged in the permanent order table so the
allocated delivery route/day can be updated.

This may only be 1 changed record out of 100 updated for a specific day -
the order reference needs to be unique can you think of a way of flagging
(formatting the colour or other) records that have changed when updated via
the import.

I realise this sounds complicated and I hope you (or anybody else) can
understand my dilemma

Best Regards
GARMANI

 

[Jeff Boyce]

Where are you considering seeing the "flag"? If you are working directly in
the tables, bear in mind that Access tables work very well for storing data,
but Access forms (and reports) are designed to display data.

Is there a way you could "flag" the records you update during the update
query/process? If you add a field to your permanent table (?the Order
table) that holds an indicator, you could use that for selecting/identifying
updated records.

One indicator could be as simple as a Y/N field. Another could be the
actual date/time of the update. Either way, these could be set in the
update query.

Or am I still misunderstanding?