Re: Update Query to Overwrite Old Data Automatically

Tech-Archive recommends: Fix windows errors by optimizing your registry

Gina,

Seems like a lot of work. Rather than trying to update the Excel
Spread***, why don't you just provide them a new spread*** 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.



"livtobeblessed via AccessMonster.com" wrote:

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


Dale Fye wrote:
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200903/1


.

Quantcast