**Append/Update Query HELP**

  • Thread starter Thread starter UBSara
  • Start date Start date
U

UBSara

HI
I have two tables in my database. The first table, New
Events, holds updated and new information for the most
recent time frame. The second table, All Events, houses
all of the events from the inception of the database.

I do not know how to update and append the information
from the New Events table to the All Events table.
Currently, my simple append query duplicates the rows of
my updatable infomation and adds rows of the new
information. The All Events table needs to have the
updated information to overwrite its existing respective
row in this table as well as capture the new information.

My attempt is to make two queries: An delete query to
remove the old, updatable information and a simple append
query to then all of these New Events to the All table.
The entire row of information needs to be captured, so a
one-to-many link will not suffice. Please walk me through
these steps I'll need to take.
Many thanks
Sara
 
Here are some ideas that might help

I would make sure that there was an autonumber column on both tables, 'NewID' in 'New Events' and 'AllID' in 'All Events.' I would make sure that there was also an 'AllID' column in the 'New Events' table so that we can link back later if we need to. (If its a new record, AllID should be set to 0 when its added, otherwise mark the ID of the record you're editing.) Finally, I might not delete records in the 'New Events' table... maybe we should add a yes/no column called 'Transfered' that we can check once we're done.

I'm assuming that this is a single-user database. We might do things in a more paranoid manner if we thought people might change things as we were working

Steps

1) DELETE * FROM [All Events] WHERE [All Events].AllID IN (SELECT AllID FROM [New Events] WHERE Transferred = FALSE)

2) Append all the records that are marked 'Transferred = false' in 'New Events' to the 'All Events' table.

3) Update the 'Transferred' field in the 'New Events' records to true

4) Occasionally I might clean out all of the records marked 'Transferred'

Jeff Johnso
Advantec Information System
http://www.advantecis.com
 
Jeff
Muchas Muchas Gracias for your suggestion. The main issue
I had was with the SQL language. I didn't need the
Transferred column, but I did add the additional column in
the New Events Table to reflect the All Events ID. (I
already had auto number columns in both tables and one New
Events ID column in the All Events Table). Thank you very
much for your help and your promptness of your reply.
Best Regards,
Sara

-----Original Message-----
Here are some ideas that might help.

I would make sure that there was an autonumber column on
Click to expand...
both tables, 'NewID' in 'New Events' and 'AllID' in 'All
Events.' I would make sure that there was also an 'AllID'
column in the 'New Events' table so that we can link back
later if we need to. (If its a new record, AllID should
be set to 0 when its added, otherwise mark the ID of the
record you're editing.) Finally, I might not delete
records in the 'New Events' table... maybe we should add a
yes/no column called 'Transfered' that we can check once
we're done.
I'm assuming that this is a single-user database. We
Click to expand...
might do things in a more paranoid manner if we thought
people might change things as we were working.
Steps:

1) DELETE * FROM [All Events] WHERE [All Events].AllID IN
Click to expand...
(SELECT AllID FROM [New Events] WHERE Transferred = FALSE);
2) Append all the records that are marked 'Transferred =
Click to expand...
false' in 'New Events' to the 'All Events' table.
 
Back
Top