From the course: Access Essential Training (Office 365/Microsoft)

What are queries?

From the course: Access Essential Training (Office 365/Microsoft)

Start my 1-month free trial

What are queries?

“

- [Narrator] Queries in 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 queries before, then you might be wondering, well, 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 combine that data into new configurations on the fly. Queries return answers in the form of a temporary data sheet, but they don't take up any additional storage space in the database because they only store the instruction on how to reassemble your data and don't store any data of their own. And every time you run a query, it fetches the current state of the data from the source tables, so 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 in fact other queries, in exactly the same way. So far, we have looked at storing our 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 a little counterintuitive when it comes to actually finding information spread between lots of tables. If, for instance, we need to quickly find Dahlia Landon's mobile phone number, we would first need to look up her employee ID, then take that information to the phone table, then find her employee ID there as a foreign key, and finally, get the 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 for us. Queries bridge the related tables back together in whatever configuration you might need. They're also very space efficient, in that they don't actually store any data. They do this by displaying selected portions of your already existing tables. The temporary data sheets that queries return are called recordsets. Recordsets merely display data. They don't actually store it permanently. This means that when your tables update, the query's recordset updates. There's no need to update data in two or more locations. Further, the recordset functions just like any of your tables when feeding records to a form or report or another query. On the surface, and as far as any other database objects are concerned, they are tables. But we know they're 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 of 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, which fields you want to ignore, and in what order to snap them back together again. The only space that queries take up in the database file is just enough to store the 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 the related content from other tables, filter and sort the results, and return them back to us in, quite literally, any configuration imaginable. And that's just the most basic thing that queries could do for you. Needless to say, queries are a very powerful component of your Access database. And the wizards and the design tools make it easy for you to harness that power.

Contents