The foundation for any database software, like MS Access, is the ability to send a query. Organizing information and making it accessible on demand is what makes database software so powerful. Understanding how to craft queries can be challenging at first, but once you understand how to use select, action, parameter and aggregate queries, then you’re on your way to expertise. For truly complex queries and more complete data sets, you need to understand what query joins are, what they do and how they work.
Types of Queries
When working with an online database, there are four basic types of query joins: inner, left, right and full. These descriptors tell you where the web database is looking for the requested information.
- Inner – An inner-query join allows you to search both tables and get results that match the query from either of the tables.
- Left – A left-query join returns results that match the query from the left table and all matching rows from the right table.
- Right – A right-query join works the same as the left join, but it uses the right table as the starting point.
- Full – A full-query join returns any results where there is a match in one of the tables.
Exploring the “Join Properties” Dialog Box
When creating join queries, you must first create joins between tables and relationships between fields. When the “Join Properties” dialog box pops up in the Access web app, you will see three sets of options:
First, you will need to select the left and right tables you want to join. There will be a drop-down list with all of the available tables. Designate the first one you want to work with as the left table and the second as the right table.
Next, you will need to select the columns to look at for each query. This allows the database on the web to only search for information in relation to those columns. There will be a drop-down list with all of the column names available. Select the column on the left and right tables you will be working with.
At the bottom of the dialog box are three options. These options are for creating a left, right or inner query. Select the type of query you want to use and move on to creating output fields.
Using join queries effectively allows you to do things like segment your mailing list for targeted marketing, find customer accounts more quickly and accurately, build customized reports and more.