A table to preserve manual updates

[cbhamlyn]

Good afternoon,

I have an Access database used for reporting monthly data. I
basically import data from a third-party software, run reports to find
errors, my users update the data in the third-party software, then I
re-improt, rinse, repeat. When no more errors remain, I need to run a
different set of reports of the "Clean" data.

There are now some fields in the data that are not updateable in the
third-party software. For these cases I need to manually adjust the
data on my end. However, everytime I re-import I have to remember to
re-adjust the data again.

What I'm looking to do is have a table of the manual changes that I
fill out as the changes come in. Then, whenever I need to re-import,
an update query is run to replace the imported data with any manual
changes in this new table.

Ideally, I see it working like this:
I open a Form/Query in datasheet view, showing the entire imported
data (after the update query is run). I make manual edits and when I
close out, any record that was modified is copied into this new
table. If there is already a record in the new table (for example, if
I've already made one correction to that record), it will be
overwritten by the changes I've made this time.

Anyone have any hints on where to start? For example, Is there away
to identify that certain records have been updated on a form?

Thanks for any advise you can give
Charles Hamlyn

 

[Lord Kelvan]

well thats not to have first you need to identify the data in the
tirdy party software when it is imported say with a primary key
field. then using that create a new table

changes table
primarykeyfield
fieldthatisupdated

it would store

1 updateto
2 updateto
4 updateto
7 updateto
12 updateto

where update to is what you want to update the field to

so you can run an update statement

update importtable
set importtable.fieldneedingtobeupdated =
changetable.fieldthatisupdated
where importtable.primarykeyfield = changetable.primarykeyfield

of course if there is no wany to UNIQULY identify the records imported
then there is no real way to do this.

what is the tirdy party software something you developed or an off the
shelf thing.

Regards
Kelvan

 

[cbhamlyn]

well thats not to have first you need to identify the data in the
tirdy party software when it is imported say with a primary key
field.  then using that create a new table

changes table
primarykeyfield
fieldthatisupdated

it would store

1     updateto
2     updateto
4     updateto
7     updateto
12   updateto

where update to is what you want to update the field to

so you can run an update statement

update importtable
set importtable.fieldneedingtobeupdated =
changetable.fieldthatisupdated
where importtable.primarykeyfield = changetable.primarykeyfield

of course if there is no wany to UNIQULY identify the records imported
then there is no real way to do this.

what is the tirdy party software something you developed or an off the
shelf thing.

Regards
Kelvan

The third party software was developed/customized in house through a
consulting firm. We have a Change Request in to allow the user to
update the fields in question, but were told it would be several
months before it was developed and tested.

Right now there are I think 2 fields that could be updated. So my
thought is to just grab the entire record (the entire table will be
roughly 15,000 records by the end of the year, and then be purged at
year end). Then all I would need to do is adjust the update query to
update those fields that would overwrite the imported data. Maybe I
need examples: This is a simplified version, and not actually my
situation:

Imported in Jan 2008:
Cust Num - Order Num - Bunch of fields - Order Comments
1234 - 123 - blah blah blah - Order is complete

In Feb 2008 we might get an e-mail that says "1234-123: This order was
returned, please adjust the comment."

So now I want to open a Form (datasheet view) displaying all the
records in the actual imported table, scroll down and find 1234-123,
and change the comment to "Order was returned". As soon as I leave
this record, I want the entire record to be copied into Table2.

Now, I re-import from the data from the third party which looks like
this:
1234 - 123 - Newly updated blah blah blah - Order is complete
(notice that the "Bunch of fields" are getting updated by the third
party software, but not the comments).
As soon as it's finished importing, I want an update query to run and
say "Oh look, I have a 1234-123 record in Table2, I'm going to replace
the Comments field in Table1 (imported table) with the comment in
Table2.

So if I were to open my form again, I would see the record with the
correct comment.

As a second piece to this, I would like to be able to further modify
the comment ("A replacement was sent out") at this point and have that
comment overwrite the first in Table2. Probably just have a delete
query remove the record from Table2 and re-add the corrected one.

Does that make sense?

Thanks again for any help.
Charles Hamlyn

 

[Lord Kelvan]

ok the table structure for the change table needs to be
custnum ordernum ordercomments

so when you update 1234-123
the changes table will store

custnum ordernum ordercomments

1234 123 "order returned"

then you use the update query

update importtable,changestable
set importtable.ordercomments = changestable.ordercomments
where importtable.custnum = changestable.custnum
and importtable.ordernum = changestable.ordernum

and just run that query every time you do an update

if you want to insert data into that table use this query

insert into changestable (custnum,ordernum,ordercomments)
values (custnumvalue,ordernumvalue,"ordercommentsvalue")

ie
insert into changestable (custnum,ordernum,ordercomments)
values (1234,123,"order returned")

and to update the changes table

update changestable
set ordercomments = "newordercommentsvalue"
where custnum = custnumvalue
and ordernum = ordernumvalue

ie
update changestable
set ordercomments = "A replacement was sent out"
where custnum = 1234
and ordernum = 123

i hope this helps

regards
Kelvan

 

[cbhamlyn]

Thanks Kelvan, that helps a lot

Have a great day
Charles