From the course: Access 2019 Essential Training

What are queries?

From the course: Access 2019 Essential Training

Start my 1-month free trial

What are queries?

“

- [Instructor] Queries and access are where a lot of the magic of the database comes to life. And truth be told, they're one of my favorite components of any database. But if you've never worked with a query before, then you might be wondering: What exactly is a query? Simply put, queries are a very powerful way to ask questions about your data. You'll do this by pulling information out of your existing data tables and combining that data into new configurations on the fly. Queries return answers in the form of a temporary datasheet. But they don't take up any additional storage space in the database because they only store the instructions on how to reassemble your existing data and don't actually store data of their own. And every time you run a query, it fetches the current save of the data from the source tables and they're always up to date. One of the best features of a query, though, is that they look and function just like regular tables. So you can connect them to forms and reports and other queries in exactly the same way. So far, we've looked at storing your data in tables. These tables relate to one another such that you can use the primary key from one table to find the related records in another table. This is a very efficient arrangement when it comes to storage, but it also seems to be a little counterintuitive when it comes to actually finding information spread between lots of tables. If, for instance, we needed to quickly find Dahlia Landon's mobile phone number, we would first need to look at her Employee ID number, then take that information over to the phone table, find her Employee ID there as a foreign key, and finally, get the mobile number. If you have hundreds or thousands of phone numbers to look up, for a human sifting through all these tables, that could be a lot of work. Luckily, we have query objects to do all of that work for us. Queries bridge related tables back together in whatever configuration we might need. They're also very space efficient. Now, they don't actually store any data. They do this by merely displaying selected portions of your already existing data tables. The temporary datasheets that queries return are called recordsets. Recordsets merely display data, they don't store it permanently. This means that when your tables update, the query recordsets update. There's no need to update your data in two or more locations. Further, the recordset functions just like any of your other data tables. When feeding records to a form or report, or, in fact, even to another query. On the surface, and as far as any of the other database objects are concerned, they are tables. But we know that they're just simply pretending to be tables. So if they look like a table and they act like a table, how are queries not tables? Well, to create a query, we simply need to write out some basic instructions on how to assemble the data. Imagine that you take all your data tables and you smash them apart. Every field is now its own building block. When you create a query, you essentially cherry pick which fields you want to pick up and which fields you want to ignore. And also, what order you want to snap them back together again in. The only space that a query takes up in the database file is just enough to store a few lines of simple instructions on how to reassemble your data regardless of how many records those tables contain. With queries, we can instruct access to take our tables apart, rearrange the fields, combine them with related content from other tables, filter and sort the results, and then return them back to us in quite literally any configuration you can imagine. And that's just the most basic thing that queries can do for you. Needless to say, queries are a very powerful component of your access database. And the wizards and design tools make it easy for you to harness that power.

Contents