How to Update Data in a Microsoft Access Query

Save
Next Video:
How to Create a Crosstab Query From Scratch in Microsoft Access....5

Understanding the concept of how to update data in a Microsoft Access query means understanding that there are certain queries in which a person will be allowed to update the data in a data sheet view. Learn about queries in which a person will not be allowed to edit data in a data sheet view with help from a computer programming teacher in this free video on Microsoft Access.

Part of the Video Series: Microsoft Access Tutorial
Promoted By Zergnet

Comments

Video Transcript

Hi my name is Chip Browne. I teach computer programming and Microsoft application classes at Stevens Henagar College here in Utah. I also create practice tests for Microsoft Office certifications. Today we are going to learn how to update data in a Microsoft Access query. Microsoft Access is a registered trademark of Microsoft Corporation. I am not affiliated with Microsoft Corporation. To introduce the concept of how to update data in a Microsoft Access query, it is important to understand there are certain queries where you will be allowed to update the data in a data sheet view and there are certain queries where you will not be allowed to edit the data in the data sheet view. One type of table and query that you can edit the data in the data sheet view is a single table when the query is based upon a single table. This, for example, is one of those queries. To go into design view you will discover that there is one and only one table that this table is built upon, that this query is built upon. Here we go and run the query and we have data sheet view. This data sheet view is very similar to a Microsoft Excel Spreadsheet view and you can actually use tabs and down arrows and this is where we can make an edit and once again we can make this edit because this query is a select query and is based upon a single table. If we are based upon multiple tables that have a one to many relationship we would not be allowed to make this edit. For example, here is a query that is based upon a one to many relationship between more than one table. If we look in the design view you can see you have got the tables here and there is, these relationships are one to many so when we run this query although it looks like we can edit the data it will not allow us to edit this data. Once again it is because the underlying query is based upon tables that have more than one to many or have a one to many relationship. This is a situation where you cannot edit the data. This is once again a situation where you can't edit the data. This query is actually an escudo query that is based upon a select statement. This is not an update query. This is literally a select statement that gives you a read, write access to the table underneath even though it is a select statement and you can have a where clause and you can create a nice filter for this set of data, it still allows you in this data sheet view to make those edits. Now what is out of scope of this discussion is to talk about update queries where you have a different type of query and that you can learn more about on another one of my videos. Likewise this is not about a make table query, by the way you have a query that is a one to many relationship you can actually make a new table from that data and then edit that data in the new table but keep in mind that that new table that you create is outside the tables that the query was built upon and any edits to that new data don't affect the underlying tables' data that were used to generate that new table and to make table queries is also covered in another one of my videos. Now for more information about how you can perhaps change a query so that you can edit its data I recommend that you go into your help and enter in the words edit data in a query. By doing that and opening up that help file you'll see down towards the bottom that this is a review of what we have just talked about and also down towards the bottom there is a section where you can make changes to a query so that you can edit its data. I recommend that you open and read that and for further information. If you have any other questions visit me, Chip Browne at chipbrowne.com.

Featured

Related Searches

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!