Append Query Question

[chewbears]

Every week I get a database dump with new and updated records for a table. The table maintains the same ID's so that the record may contain updated fields but the id never changes. So I get a few updated records and a few additional records. Rather then bringing this in as a new table every week, how could I make the append query, import the new table by overwriting the old table? I need this to be press of a button easy.



Thanks

[hertfordkc]

Every week I get a database dump with new and updated records for a table. The table maintains the same ID's so that the record may contain updated fields but the id never changes. So I get a few updated records and a few additional records. Rather then bringing this in as a new table every week, how could I make the append query, import the new table by overwriting the old table? I need this to be press of a button easy.

Thanks

1) Import the new replacement/additional info as Newdata.
2) Run a query to delete a record from OlData if the OlData.ID=Newdata.ID
3) Run a query to append NewData to OlData
4) Delete Newdata

While some SQL guru may be able to do it in one step, I would put those four steps in a macro which is connected to a button somewhere.

[chewbears]

1) Import the new replacement/additional info as Newdata.
2) Run a query to delete a record from OlData if the OlData.ID=Newdata.ID
3) Run a query to append NewData to OlData
4) Delete Newdata

While some SQL guru may be able to do it in one step, I would put those four steps in a macro which is connected to a button somewhere.

Not to be a complete newbie, but what would be the syntax for the delete query?

[June7]

Review:
http://www.w3schools.com/sql/sql_drop.asp

[chewbears]

Review:
http://www.w3schools.com/sql/sql_drop.asp

Why would it be a drop and not a delete?
DELETE
* FROM OlData
WHERE NewData.ID = OlData.ID;

[hertfordkc]

Not to be a complete newbie, but what would be the syntax for the delete query?

-----------------------------------------------------------------------
There is a reason that I'm an 18 year novice. I haven't bothered to learn SQL. Designing a query from scratch and combining queries have solved all of the problems I've come up against.

In Access 2007, go to Create query, and bring in the OlData table and the newdata table. You need to create a join which gives you the records in Oldata which have ids in the newData.
Once you've run that a few times and are satisfied with the result, click on the delete query button. If for some reason you need to runsql instead of openquery, after you have made it a delete query, choose SQL view.

After you've done this a few times, you'll be able to understand SQL even though you may never speak it.

[chewbears]

-----------------------------------------------------------------------
There is a reason that I'm an 18 year novice. I haven't bothered to learn SQL. Designing a query from scratch and combining queries have solved all of the problems I've come up against.

In Access 2007, go to Create query, and bring in the OlData table and the newdata table. You need to create a join which gives you the records in Oldata which have ids in the newData.
Once you've run that a few times and are satisfied with the result, click on the delete query button. If for some reason you need to runsql instead of openquery, after you have made it a delete query, choose SQL view.

After you've done this a few times, you'll be able to understand SQL even though you may never speak it.

Thanks I have the two queries set up, but I am struggling making them work together. Forexample: I run the above SQL to delete. Works great. But when I do the append it doesn't look to the query result. It just looks at the 2 tables and appends. How do you make them work together?

[hertfordkc]

Always listen to June7.

[June7]

Sorry, I thought you needed to know how to remove the temporary NewData table/records.

Delete from OlData based on records in NewData will be a little more complicated than your example. Check out DELETE FROM WHERE EXISTS
http://www.techonthenet.com/sql/delete.php
http://allenbrowne.com/subquery-01.html

I've never had to use it, but have referred posters to this before.

[chewbears]

Think I am catching on June:

DELETE * FROM OlData
WHERE EXISTS
( select *
from NewData
where NewData.ID = OlData.ID );

Now just gotta make the append work after that fires.