MS Access Append Query Example For MS Access versions 2000, 2002, 2003, 2007Go here to see the Append Query in SQL ViewGo here to see the Append Query in VBA This is a basic Append Query tutorial, in it we will walk through the Append
Query in MS Access. For the most part all versions of MS Access, from 97 to
2007, the query builder appears the same. There will be some small differences
throughout all versions. This tutorial will be dealing with MS Access 2000-2003
but includes some instructions for 2007. The final MS Access Query Example and
how it should look will be at the bottom of the page. Keeping in theme with a
previous tutorial we will use the same table and field names. See the
Table Design query if you need help designing a table. 
Let us assume that our table is populated and that we want to transfer some data to another table. One reason could be you have a list of addresses you need to send mailings to on a regular basis. Having these addresses in a table of their own would be convenient. You could populate the new table a couple of different ways. The way we will do it in this tutorial is via the Append query.
To design the query using the query builder click "Objects" in your left hand
menu and then "Queries" next.
You will be given the choices between "Create query in Design view" and "Create query by using wizard". This article will not deal with "Create query by using wizard". Now double click "Create query in Design view". You will then be presented with the window called "Show Table", in it will be the table named CUSTOMERS or whatever tables you may have. Click ADD and then Close.
|
|
Now you will be in the Query builder named Query1. You will see this in the top left corner followed by a colon. Click the Query Type Button and select "Append Query" for versions 2000-2003.
In version 2007 ensure that you are in the "Query Tools Design" Tab and then select "Append". Click the picture below to enlarge.
You will then be presented with a dialog box Named Append. Choose the table you wish to append to using the drop down box and click ok...
If you do not have a second table to recieve the data you wish to append using the "Append Query" make it now.
This next part can be done by using all fields IE “CUSTOMERS.*” provided the
receiving table has the exact same fields. With that said be aware that if both
tables have an Autonumber field or Unique ID field there will be issues. Even
if it is not Primary key you will risk appending duplicates into said field if
it already has data. You should drag all the fields down that you wish to carry
to the new table except Autonumber fields if the receiving table has data
already. As for fields with Unique IDs you can bring those across provided you
know the data being appended will not be repeated.
This is assuming that your table receiving the records has the equivalent
fields to accommodate the data. You will notice that as you drag fields down
below that the Query builder automatically populates the “Append to:” row if
the field names are the same. If the field names are not the same you will have
to select those manually. Other wise in the Field row add your field name; say
the USE field and the data. It should look like this like this: USE:"Your data
Here". The data you wish to add must be in quotes unless it is numeric.
Now you should be ready to complete the Append. Go ahead and click the run button (red Exclamation point on the toolbar). You will be presented a confirmation box, click "Yes" if you are sure you want the Append to complete. If you are ready, click the Run button to execute the Append Query and MS Access will Append the Data.
That is it; you have now completed your Append Query.
You may also wish to see these tutorials:
Append
Query in SQL
Update
Query Tutorial
You may post this tutorial on your website or in a forum. If you do please maintain a Link to Eraseve AP.
| Did you find what you were looking for? What would you suggest? mail@eraserve.com |
