Microsoft Access has 4 queries that change data in the tables (or create new ones) in the database that you are using.
They can help you create a new table to share specific data with others, add data from one table to the bottom of another, update information with changes or even delete what is no longer required.
Make Table Query
A make table query does exactly what it says. It creates a brand new table from the tables or queries that already exist in the database.
1. The query is started like any other where the data to be extracted is chosen.
2. From the Design tab in the query design view Make Table is selected.
3. The table is given a name and it is chosen whether this table will be situated in this data base or another one.
4. The query is saved. At this point the new table has not been created
5.To create the new table click on the Run icon.
6. You will be informed the number or rows that will be pasted into a new table and get warned that you cannot undo your actions.
7. However with this type of query, if the result is not what you require then you need to delete the new table created and start again.
1. In the query design select the fields you want to add to another table. There could be criteria added if you don’t want to append all the data.
2. Click on the append icon and choose the table you want to add this data to.
3. You will get an extra row in the QBE window at the bottom where you may need to match some of the fields if the names are different in the 2 tables.
4. Save the query.
5. To append the data Run the query
6. You will be told how many rows will be added and that the action cannot be undone.
An update query actually updates specific data in a table. This may be used in conjunction with criteria for example if you wanted get consistency in your data, for example updating Company XYZ Ltd with Company XYZ Limited for consistency. It can also be used on all data in a specific field for example if prices were increased by 10% across the board.
1. In the query design add the field that needs to be updated and a criteria is necessary.
2. Click on the Update icon
3. A new row appears in the QBE window called update to. This is where you put the change you need to make.
4. Save the query
5. To Make the changes Run the query
6. You will be told how many rows will be changed and that the action cannot be undone.
Be careful not to run this query multiple times if it is providing a 10% increase as every time it is run the prices would increase by another 10%
Delete queries can be run to delete data in a table. This could be because you want to get rid of data that is before a certain date or maybe a client is no longer with you and you need to delete their related data.
1. In the query design add the field that contains the criteria that needs to be deleted.
2. Click on the Delete icon
3. A new row appears in the QBE window where the criteria to determine the data to be deleted can be added
4. Save the query
5. To delete the data Run the query
6. You will be informed how many rows will be deleted and the action cannot be undone
NOTE: You may choose to have an identical database containing the deleted data by using an append query before running the delete query. This would provide a backup should you need to get the data back in the future. However by removing the extra information from the database you use on a regular basis queries will run more quickly on a day to day basis. This can be vital once your database contains huge volumes of data.
NOTE: If you need to edit any of the action queries go straight into design view. If you double click on the query it will be rerun and could have a detrimental effect on your existing data.
If you’ve enjoyed reading about how Action Queries that change data in your Microsoft Access tables, there’s some other blogs below that you might find useful: