What are queries?

Keyboard Shortcuts

Queries in Access are where a lot of the magic of the database happens. In this video, Adam Wilbert gives an overview of the role of queries which can reassemble related data tables in unlimited configurations, and quickly surface answers to complex questions that users have about the data.

- [Narrator] Queries in Access are where a lot of the magicof 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 wayto ask questions about your data.You'll do this by pulling informationout of your existing data tables,and combine that data into new configurations on the fly.Queries return answers in the form ofa temporary data sheet,but they don't take upany additional storage space in the databasebecause they only store the instructionon how to reassemble your dataand don't store any data of their own.

And every time you run a query,it fetches the current state of the datafrom 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 anothersuch that you can use the primary key from one tableto find the related records in another table.This is a very efficient arrangementwhen it comes to storage,but it also seems a little counterintuitivewhen it comes to actually finding informationspread between lots of tables.

If, for instance, we need to quickly findDahlia 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 thousandsof 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 togetherin 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 portionsof your already existing tables.

The temporary data sheets that queries returnare 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 functionsjust like any of your tables when feeding recordsto a form or report or another query.On the surface, and as far asany 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 instructionson how to assemble the data.

Imagine that you take all of your data tablesand you smash them apart.Every field is now its own building block.When you create a query,you essentially cherry pick which fieldsyou 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 fileis just enough to store the few lines of simple instructionson how to reassemble your data,regardless of how many records those tables contain.With queries, we can instruct Accessto 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 thingthat queries could do for you.Needless to say, queries are a very powerful componentof your Access database.And the wizards and the design toolsmake it easy for you to harness that power.

Resume Transcript Auto-Scroll

Author

Released

9/24/2018

Learn how to build Access databases to store and retrieve your data more efficiently. Access expert Adam Wilbert explains the concepts behind relational databases, before moving onto building tables—the foundation of any database. He then demonstrates how to define the relationships between tables and use queries to find and filter data. This course also shows how to apply rules and validation to minimize data entry errors; build an interface for the database from forms, complete with interactive buttons; create reports for printing and sharing; and take steps to maintain the database you created.