Almost everything you want to do can be done by a novice. But you need to learn a couple of good habits BEFORE you put this into production.
1. Before making a big change that has the chance of losing data, make a copy of your DB. Work on the copy. Make a copy of the copy in case your first attempts don't quite work like you wanted. Don't go into production until your solution works reliably on your test version. At least twice.
2. Before implementing this in production, keep a copy of the pre-change DB anyway as a reference point. You will trash this copy ONLY when the change has worked correctly for at least a month or two.
3. Since you are going to be moving records around, you are going to have lots of free-space manipulation within the .MDB file, which eventually leads to DB "bloat" - which is a performance and free-space thief. So if you had not planned for one before, put a little bit of down-time in your regular schedule for a repair, compact, and backup session.
Monitor your database size daily. Figure out its growth rate. (Trust me, it will grow.) You want to pick some arbitrary but comfortable number for how big your DB can grow before you need to clean it up.
For my biggest DB, 25% was the size we chose, that was how big it grew in a week. So we compacted and all that other stuff weekly. It could be different for you. And I reiterate, 25% growth in size was arbitrarily chosen as our "comfort zone." Yours can differ and there is no wrong answer - except to not maintain the DB at all.
Frequently, you face a balancing act. If your comfort zone is too low, you have to do daily maintenance. That is itself a type of DB handling for which errors can occur. So mucking about too much is not good. But the other side of that coin is that a very high comfort zone ties up lots of resources on disk (as the MDB grows) and in the system memory ('cause you need as much swap space as the size of Access plus the size of the .MDB file) Also in CPU time because the more growth, the more fragmented the .MDB has become, and that fragmentation slows down allocation operations.
Finding this balance between growth and frequent maintenance is hard. Like flipping a coin and hoping that it comes up neither heads nor tails, but instead comes up "edge."
4. OK, down to the business at hand.
If the fields in the one table are the same as the fields in the other table, is there a particular reason why you wanted them separate? (Other than the obvious benefits of staging them so you could decide to cancel the update)? If there are no other differences, you can either
a) Add a yes/no field that converts something from incoming to assigned without moving it. Then import directly to the materials table. To cancel the transactions, erase the items still marked as incoming. Less data movement = fewer risks. (BTW that last statement is true whether we are talking stand-alone MS Access or ORACLE 10i Parallel Server with Advanced Security Options...)
b) Closer to being direct in line with what you asked...
Write an append query. Start with a select query based on the "Incoming" table, but before you add any fields, in query design grid, go up on the toolbar and change the query to an append query. It will ask you for the table name. Tell it. Then name each field to be appended. If the field names and sizes are the same in both tables, Access will make the correct "lucky guess" every time you select a new source field.
Now write an erase query. Start with a "SELECT * FROM INCOMING;" and then you change the Select type to a Delete type in the toolbar.
Now write a macro. Turn off the warnings (SetWarnings,option Off; it's one of the possible macro actions), then run the two queries from the macro in the sequence append, delete. Those are RunSQL, I think. You name the query to be run as one of the options. Turn on warnings again. Stop the macro. (StopMacro)
Last step. On the form from which you wish to control this action, go into design mode. Enable the control wizards. Create a control button. The wizard will ask you what you want it to do. You will have a whole multi-segment list of possible options. Tell it you want to run a macro. (Hint: You just created a convenient macro in the previous step.)
One final bit of advice. As you grow more comfortable with the VBA side of doing things, revisit this button so you can rewrite the code generated by the button wizard. Wizards give you very convenient starting points. But they are terribly dumb. What you can also do is convert the macro to VBA and run the VBA in the OnClick event for that button. But only do that when you are ready.
Why? 'cause the only thing dumber than a wizard is a macro. No error checking worth mentioning, no logic ability to handle even the slightest variation on any themes, and generally a clumsy way to do some things. But even the experts advise them as starting points for novices because they don't give you an impossible number of options that could overly confuse you. Sometimes a pre-packaged action is the right starting point. As a starting point not intended to hang around forever, they are OK. Sometimes you have to choose little steps first rather than take that flying leap off the VBA precipice.
