Update Query to Overwrite Old Data Automatically

[livtobeblessed]

Hi I have a database with many fields, and approximately 5,000 records. Each
month I have to provide an updated database to my mail vendor. Some fields
such as contact name, address, title and company name may have been updated.
Currently out of the 5,000 records I am using about only 1,000 of those
records as an active mail list. I have to compare use a unique identifier
to compare to the full mail list to build my updated mail list on. In order
to do this, I am manually appending over the 1,000 records because I cannot
tell what particular records have an update. Is there an easy way for me to
update the 1,000 records from the master of 5,000 by overwriting the 1,000
records from the 5,000 that carry the same unique identifier? I want to be
sure this becomes more of an automatic process than manual.

My second question is, within the same two lists, master update for appending
to mail list file, there may be some records that I'll need to delete because
that unique identifier is no longer existing in the larger file which mean a
firm has terminated and therefore falls off the mail list. How do I run an
update for deletions and would those records automatically be removed from my
mailing list?

Hopefully my inquiries is clear but if you need me to clarify anyting, please
ask and I'll try to clear up my question(s).

Thank you.

Gina

 

[Dale Fye]

If you an an autonumber or some other unique ID, you could use this to join
the 5000 record table and the 1000 record table you are exporting, but why
not just provide your mail vendor with an entirely new list?

The way I would do this is to keep track of the records that have been
updated by adding a [LastUpdate] field to the table. Then, in the
BeforeUpdate event of the form that you use to update the records (assumes
that you are actually using forms), I would include a line that sets the
[LastUpdate] field to the current date.

Then, if you really need to update the 1000 record list, you can join the
two tables on the unique ID, and run an update query to update only the
records that have been updated.

To track deletions, I'd consider adding a [DeletedOn] field to keep track of
the date the record was removed or had it's status changed to "inactive".
Then you could simply join on the unique field, and delete from the 1000
record table for those records that match the unique ID and which are
"inactive" or have a value in [DeletedOn].

HTH
Dale

 

[livtobeblessed via AccessMonster.com]

Hi thank you for your reply Dale. No I actually was not using form, the
databases are originally in Excel. I import them into Access, create tables
and then I use the unmatched query wizard in Access to determine what firms
are non active. Then I go to our intranet to a program where I can
individually look up each firm to confirm the firm's status, and if inactive,
I manually delete them from the file. I then take a fresh firm's list
download of 5,000, sort it by unique ID, then sort my mail file 1,000 records
the same way and then I copy and paste fromo the updated list of 5,000
records over the matching 1,000 records, one by one. Note this list of 1,000
is growing because of new subscribers.

I know that I am certainly doing this a super long way and it takes too much
time and I have so many different job functions that I do not have the time
to spend to do this each month and take so much time getting it done.

I am still pretty fresh to Access because I just do not use it often enough
to know the tricks and so forth in Access, but I am trying to become more
accustom to it. The database does already have a last updated field.


I'll try to work this in forms to see if it solves my problem. Thank you for
your help.

Gina

If you an an autonumber or some other unique ID, you could use this to join
the 5000 record table and the 1000 record table you are exporting, but why
not just provide your mail vendor with an entirely new list?

The way I would do this is to keep track of the records that have been
updated by adding a [LastUpdate] field to the table. Then, in the
BeforeUpdate event of the form that you use to update the records (assumes
that you are actually using forms), I would include a line that sets the
[LastUpdate] field to the current date.

Then, if you really need to update the 1000 record list, you can join the
two tables on the unique ID, and run an update query to update only the
records that have been updated.

To track deletions, I'd consider adding a [DeletedOn] field to keep track of
the date the record was removed or had it's status changed to "inactive".
Then you could simply join on the unique field, and delete from the 1000
record table for those records that match the unique ID and which are
"inactive" or have a value in [DeletedOn].

HTH
Dale

Hi I have a database with many fields, and approximately 5,000 records.
Each

[quoted text clipped - 34 lines]

Gina

 

[Dale Fye]

Gina,

Seems like a lot of work. Rather than trying to update the Excel
Spreadsheet, why don't you just provide them a new spreadsheet each month?

You say you go online (your intranet) and lookup each firm to confirm their
status. Is this some sort of web based database application? If so, then
you might be able to avoid the download process altogether if you can get
whoever owns the data for this application to give you permission to link
their table(s) from whatever their data store is (SQL Server, Oracle, MySQL)
directly to Access (they could even give you read only permissions, to
prevent you from accidentally altering their data).
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Hi thank you for your reply Dale. No I actually was not using form, the
databases are originally in Excel. I import them into Access, create tables
and then I use the unmatched query wizard in Access to determine what firms
are non active. Then I go to our intranet to a program where I can
individually look up each firm to confirm the firm's status, and if inactive,
I manually delete them from the file. I then take a fresh firm's list
download of 5,000, sort it by unique ID, then sort my mail file 1,000 records
the same way and then I copy and paste fromo the updated list of 5,000
records over the matching 1,000 records, one by one. Note this list of 1,000
is growing because of new subscribers.

I know that I am certainly doing this a super long way and it takes too much
time and I have so many different job functions that I do not have the time
to spend to do this each month and take so much time getting it done.

I am still pretty fresh to Access because I just do not use it often enough
to know the tricks and so forth in Access, but I am trying to become more
accustom to it. The database does already have a last updated field.


I'll try to work this in forms to see if it solves my problem. Thank you for
your help.

Gina

If you an an autonumber or some other unique ID, you could use this to join
the 5000 record table and the 1000 record table you are exporting, but why
not just provide your mail vendor with an entirely new list?

The way I would do this is to keep track of the records that have been
updated by adding a [LastUpdate] field to the table. Then, in the
BeforeUpdate event of the form that you use to update the records (assumes
that you are actually using forms), I would include a line that sets the
[LastUpdate] field to the current date.

Then, if you really need to update the 1000 record list, you can join the
two tables on the unique ID, and run an update query to update only the
records that have been updated.

To track deletions, I'd consider adding a [DeletedOn] field to keep track of
the date the record was removed or had it's status changed to "inactive".
Then you could simply join on the unique field, and delete from the 1000
record table for those records that match the unique ID and which are
"inactive" or have a value in [DeletedOn].

HTH
Dale

Hi I have a database with many fields, and approximately 5,000 records.
Each

[quoted text clipped - 34 lines]

Gina