Appending records

[StephenD]

Ok here's my problem (challenge!)....
I have one table with approx. 1 million records with the following fields: Membership ID, Joining Date, Cancellation Date

Each month I will be appending new data to the table which will have the same fields.
I don't want to have any duplicate membership Id's in the table, therefore if a membership Id appears on the monthly file I want to add this to the table and replace the old record. If a membership ID does not appear on the old file then I want to keep the original record. Finally if the membership ID does not appear on the old file but is on a monthly file then I want to add it to the table.
Has anyone got any ideas on how I will go about this-I presume I will need a couple of matching/unmatched queries and an append query??

Thanks,

StephenD

 

[Pat Hartman]

Look up the followint article in the Microsoft knowledge base:

ACC: Updating and Appending Records in One Update Query (Q127977)
http://search.support.microsoft.com/kb/c.asp?ln=en-us&sd=gn

 

[StephenD]

Thanks Pat,

Is it fairly easy to setup a procedure that will run a number of queries at the hit of a button? I have a hefty number of records in my database and each query I run is taking 1-2 hours, it wolud be nice to just press a button at the end of the day then come in the office in the morning with all the queries run.
Basically I have an update query to update about a million records, I then need to find new records that are not in the existing file and append these. I reckon I need 3 queries:-
1. The update query
2. Find unmatched query (make a table with this data)
3. append the records in the newly created table in step 2


Thanks again for all your help

 

[Pat Hartman]

To answer your question. Yes, it is quite easy. Use a macro or VBA to run a series of queries. However, did you look at the article I directed you to? A query such as it describes will update existing rows and insert new rows with a SINGLE query and therefore will take far less time than running three queries.

 

[StephenD]

Thanks Pat,

I did have a quick look, although I think my situation is a bit different from the example in the article-I basically have a set of 1 million records, 40% of these will never change and will not be in the new files I will be recieving. That leaves around 600,000 records which may or may not be amended in the new file, plus completly new records that need to be added to the existing file. The article suggests matching the new file to the existing file, but the 40% of records that will never change will never be in the new files-Does this make sense?
I think I still would need more than one query, would you agree?

Thanks again

 

[Pat Hartman]

NO!

Reread the article and try to understand what the outer join is causing to happen. Create the example so you can test it out.

 

[jesseb]

If you want to run a query by pushing a button and a button to a form and go through the wizard. If you select Misc. there is a choice to run a query. Then it lets you select which query to run. If you want to run more then just right click the button you just made (while the form is in design view) and choose Build Event. Look at the code and just copy and paste then edit where the query name gets called. That is one easy way to do that.

 

[StephenD]

Thanks Pat, I now understand! Sorry for being a bit slow!
Thanks JesseB, I 'll give it a try!