Appending to and Updating a Table

[Cathy]

I have created a query to append new data to a table.
This table contains date fields and numerical data. Each
month I get a new file that needs to be appended to the
table. But I also need to replace old data with new data
based on the date field. For example, if there is
existing data for the date 01/08/2004 and the new file
also contains data for 01/08/2004, then I want to replace
the existing data with the new file. Any suggestions?

TIA
Cathy

 

[Guest]

hi,
sketchy on details but you do have a two part operation.
you can't have a query that appends and updates at the
same time. you will have to do the append query first then
do an update query. you might be able to combine the two
parts in to a single macro.

 

[Cathy]

Sorry for the sketchy details. I was trying to avoid
being lengthy.

I'm not sure if I'll be able to do your suggestion
though. I'll explain my problem in more detail. The
monthly file I receive from another department contains
weekly data by area and the week is listed according to
the Saturday date (mm/dd/yyyy). For example, the old file
will contain weeks 1 (12/04/2004), 2 (12/11/2004), 3
(12/18/2004) and 4 (12/25/2004) of December 2004 and the
new file will contain weeks 1 (1/1/2005), 2 (1/8/2005), 3
(1/15/2005) of January 2005 AND week 4 (12/25/2004) of
December.

If I append the file then there's no way for me to
determine which 12/25/2004 data is more current (at least
none that I'm aware of.)

Thanks again.

 

[Guest]

Will you always replace the old data with new data? If so, Anonymous'
suggestion was accurate, you'll just want to run the Update Query FIRST and
then the Append Query. Does that help?

 

[Van T. Dinh]

Actually, a Update Query with proper Outer Join will also _append_ Records
from the Table being used as the source for update to the Table being
updated.

 

[Cathy]

It worked. I had to update first, set a couple of fields
as primary keys and then append. Thanks to you all!