Append/Update

[chamlyn]

Good Morning!

I am wondering if there is a simple way to backup one table (a monthly
extract) to a 2nd table (historical backup). So here's the situation:

Each month I get an extract containing customer account information.
The typical customer name, account number, account type, balances and
so on. I have decided that rather than keeping multiple copies of the
same monthly extract (50,000 records a pop) I would like to just keep
a historical record of every account we have ever had on our system.

So let's say for argument sake, I've got this historical table set up
and it's got 50,000 records. I then get the current month's extract.
It has 48,500 records, where 1,000 records have been deleted and 500
new ones added. Additionally 5,000 records have been updated in some
way.

I need to add the 500 new records to the 50,000 in the historical
table. I then need to update the 5,000 records that need to be
updated. Because I need the history, I will NOT be removing the 1,000
accounts that were deleted this month.

This to me seems like one of the first things someone designing
database software would want to implement, and yet the only way I can
find to do it is to first to an append query, then do an update query,
keeping my fingers crossed the whole time.

So my question is, Am I missing something really obvious or is that
just the way Access does things?

Thank you very much for your time and any advice you can give me!

Charles Hamlyn

 

[Ron2006]

Good Morning!

I am wondering if there is a simple way to backup one table (a monthly
extract) to a 2nd table (historical backup). So here's the situation:

Each month I get an extract containing customer account information.
The typical customer name, account number, account type, balances and
so on. I have decided that rather than keeping multiple copies of the
same monthly extract (50,000 records a pop) I would like to just keep
a historical record of every account we have ever had on our system.

So let's say for argument sake, I've got this historical table set up
and it's got 50,000 records. I then get the current month's extract.
It has 48,500 records, where 1,000 records have been deleted and 500
new ones added. Additionally 5,000 records have been updated in some
way.

I need to add the 500 new records to the 50,000 in the historical
table. I then need to update the 5,000 records that need to be
updated. Because I need the history, I will NOT be removing the 1,000
accounts that were deleted this month.

This to me seems like one of the first things someone designing
database software would want to implement, and yet the only way I can
find to do it is to first to an append query, then do an update query,
keeping my fingers crossed the whole time.

So my question is, Am I missing something really obvious or is that
just the way Access does things?

Thank you very much for your time and any advice you can give me!

Charles Hamlyn

As far as I can tell that is the route, although you can actually
combine the append and update into a single query.

I would add two fields to the "Backup Table"

1) Date created (or date of original information)
2) Last update date (last date that this record received an update.)

I have done similar things and have always found this to be a set of
information that has come in handy.

Ron

 

[chamlyn]

As far as I can tell that is the route, although you can actually
combine the append and update into a single query.

I would add two fields to the "Backup Table"

1) Date created (or date of original information)
2) Last update date (last date that this record received an update.)

I have done similar things and have always found this to be a set of
information that has come in handy.

Ron- Hide quoted text -

- Show quoted text -

I was hoping for a more automated process. Combining the two seperate
queries into one isn't going to help me out all that much, so I'll
leave them seperated for simplicity's sake (and the sanity of whoever
replaces me someday).

I had planned on adding a "last updated" field, but hadn't considered
the "date created" field. Great idea.

Thank you very much!
Have a great day.

Charles

 

[Ron2006]

I was hoping for a more automated process. Combining the two seperate
queries into one isn't going to help me out all that much, so I'll
leave them seperated for simplicity's sake (and the sanity of whoever
replaces me someday).

I had planned on adding a "last updated" field, but hadn't considered
the "date created" field. Great idea.

Thank you very much!
Have a great day.

Charles- Hide quoted text -

- Show quoted text -

Have a great one and Merry Christmas.

Ron