Filter Your Data with Access 2013

AppId is over the quota
AppId is over the quota

This post was written by Doug Taylor, a Program Manager on the Access team.

As you manage your business in an Access 2013 web app, the amount of information it holds will naturally grow over time.  The default views and navigation are great for getting started quickly, but eventually, wading through all of that information by scrolling through lists may not be the most efficient setup.  Is there an easier way to create a different view of the data?  In Access 2013 web apps you can create a customized filtered view in just a couple of minutes.

For example, let's say you work for a marketing company that deals with local Car Dealerships and you have created an Access app to track the projects you manage for your customers.  If so, you might end up with a set of Projects that looks something like the example below.  Here you have a list of all your projects from A-Z. 

Over time, you will have quite a few projects to manage, so you may start to break them into categories as we have above.  Each project in this list belongs to a category: Research, Marketing, or Design.  As you can see, the Templeton Triangle project shown above is a Research project.

Now, one of your research assistants - let's call him Dave - asks if you can create a view to help him see an overview of current and upcoming Research projects.  So, you take a minute to talk with Dave and the two of you decide that a view like the one shown below would be great for him.  This is a list of all Research projects, sorted by Priority and Start Date.

To create a view like this, start by creating a new query - open up the app in the app designer and click on Advanced->Query in the ribbon.

This will open the Query designer where you can setup some rules to filter and sort a set of data.

Choose the source table for your query.  If you want to see some of your Customers, then you would likely choose the Customers table here.  In our example, we're going to select Projects. 

Once selected you'll see the query design screen.  Here you can double click on fields from the Projects table to add them to your query. 

Selecting fields this way determines which of them you would like to see and use in the new view.  As we decided with Dave earlier, we're going to select the following fields:

IDPriorityStart DateProject NameStatusCustomerCategory

NOTE: If you would like to EDIT from the view, then you MUST INCLUDE an ID field in the query as we have done here.

Now that you have chosen the fields, there are two things left to do. 

First, Dave wants to see the Projects in PRIORITY order first, followed by START DATE.  To do this, fill in the "Sort" box in each of these fields as Ascending (i.e. A to Z).

Second, since Dave is a research assistant, he's only really interested in Research projects.  So, we'll want to filter out any other Project types as well.  To do this, find the Category field in the query and add the CRITERIA "Research" (including the quotes).  This tells Access to only show Project that have a category of Research. 

When you're finished, the query should look like the example below.  Now you can save it, and give it a name.  We've named our example "Projects_Research Only".

Great!  Now that we're done with that, we can go about building the view.  Go back to the App Designer home screen, navigate to where you would like to add the view, and click on the  button.  Give the view a name, select Datasheet for the View Type, and be sure to select the Query you just made as the Record Source.

Now, when you click "Add New View" you will have a nice summarized view of your Research projects, sorted by Priority and Start Date. 

And that's how to design a simple filtered view in Access 2013! Try it out for yourself using the Office 365 preview. In order to try out Access 2013 Web Apps, be sure to choose one of the plans for business: Small Business Premium or Enterprise.

View the original article here