How To Add, Find And Query Data In Access To Database

One of the chief benefits of Microsoft Access over Excel is the power of Admission queries and reporting. Agreement Access queries, withal, isn't ever easy. This article gives you an introduction to the Access query designer, and tips for creating advanced Access queries to save you lot hours of time.
Bookmark this page

What is a Query in MS Access?

Simply put, a query is a manner to combine data stored in separate tables to avoid duplication in the tables themselves. For example, yous accept a table for customers, and a table for contacts. But you demand to send a mailing to each contact using the client's address. You can employ a query to show each contact, the customer name, and the mailing accost of the customer. Information from separate (but related) tables, without needing to duplicate anything in the contacts tabular array.
Admission queries are very powerful, assuasive you to view, insert, delete, and calculate records and fields from numerous tables in your database.

Building a Basic Query

Nosotros'll commencement by building a basic Access query joining iii tables. Say your database contains a table for Customers, Customer Types, and Orders. Start past selecting the "Create" Ribbon tab. Select "Query Design"
Create Access Query Design View
You lot will exist prompted to select the tables and/or queries to include in your query. Select each tabular array and click "Add together" or double-click them. Recollect to click "Shut" when you are done adding your tables/queries. If your database relationships have been divers correctly, Access will automatically describe the relationship lines between your tables.

If you don't see relationship lines betwixt your tables

MS Access Query Design

when y'all add together them, you will desire to describe them manually. To do then, you lot'll demand to select one field, and drag it to the related field in the other table. Without that step, Access volition not empathize how to link the records together betwixt the tables. A few key things to remember when drawing the relationship lines:

The two related fields demand to take the same data type (eastward.thou., Text or Numeric)
Typically field will be the primary key of the table, and the other will exist a respective foreign key.
If you employ a query within your query, you volition always need to define the relationships to any other table/query manually.

Now you lot can brainstorm dragging fields from each tabular array/query into the field section of the query design window.

MS Access Query Drag field criteria
As shown above, we've added the Company Name, Customer Type, Club Date, Send Appointment, and PO number from three separate tables into one view. Adjacent click the "Run" icon on the ribbon (the red exclamation mark) and we run across the Query View:
Access Select Query View
The view higher up looks much like a standard Admission table, when in fact information technology is a consolidation of several tables. In this view, you can consign your data to Microsoft Excel, PDF, or other formats.

Sorting and Filtering Data

One time you accept a basic query in place, you'll want to be able to filter your records to perhaps display merely certain records that match your criteria, or sort past one or more fields.
Microsoft Access Query Criteria

In the screen above, we have sorted the records past CompanyName (Ascending ways A-Z) and by OrderDate (Descending means newest dates first). Note that the CompanyName field is listed first from the left, so the listing will be sorted by Company Name, then if there are multiple records for a company, the newest lodge will be shown outset. In the Criteria section, we are filtering to shown any record where the Visitor Name contains "Research." the "Like" statement is very powerful, and lets you observe records that begin with a word (Like "How-do-you-do*"), end with a give-and-take (Like "*Hello"), or contain a give-and-take (Like "*Hello*"). All based on where you place the asterisk. Note that CompanyName is a text field, and so the criteria needs to exist incorporate in quotes. Next, we're going to exclude any records where the CustomerType is "Residential" using the Not Equal to (<>) sign. We likewise want only records where the Order Date is less than 1/1/2008. Note that the pound sign (#) is used to contain dates in Admission.
Lastly, find that the 2nd line of the Criteria department starts with "or:" and we've entered a criteria. You tin can use the second line to incorporate dissever criteria sets. In this case, we want either whatever Company with "Research" in the name that is non a Residential client type and has orders earlier i/1/2008 OR whatsoever record where the Transport Appointment is empty (that is what "Is Null" means).
If you have fields in your query you want to filter or sort by, but you lot don't want to see them in the actual results view, simply uncheck the "Show" checkbox for that field. Access will still use information technology as criteria for sorting/filtering, only hide it in the Query View.

Editing Data in a Query

Depending upon how complicated your Access query is, you may exist able to edit information. Just don't exist alarmed if y'all receive a message "Tape Locked" or "Cannot Add Record." When you join multiple tables, Access may need extra guidance for how to handle edits or a new record. And remember that with a relational database, editing a single row in a query can really alter the value for all records. Take for example our field "CustomerType." Changing the value in i row volition actually alter the text value in all records since we are modifying the field in the table tblCustomerType, not the underlying identifier field in the Client table:
Access Query Lookup Value

Why Are Records Missing in My Admission Query!

You may be alarmed to observe that you've created a query that returns no records, or many fewer than yous await! Either your criteria is besides strict, y'all've joined tables incorrectly, or, you need to ascertain the relationships betwixt the fields more precisely. Taking our case query, recall that we joined the field CustomerTypeID and fk_CustomerTypeID in the Client Type and Customer tables, respectively. What if yous have records in the Client table that oasis't been assigned a Customer Type value? They will be missing from your query! That is because Access, past default, assumes you want to see simply records from ii joined tables where the values match. An empty value on a numeric AutoNumber field won't match! Fortunately the remedy is quite simple. You lot'll want to double-click the relationship line and explicitly tell Access you want to see all records from the Customer tabular array fifty-fifty if there is no matching tape in the Customer Type table:
Access Query Join Properties