Back
Access

Filter Your Data with Access 2013

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:

  • ID
  • Priority
  • Start Date
  • Project Name
  • Status
  • Customer
  • Category

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.

Join the conversation

14 comments
  1. That’s a god HowTo creating a view for growing amounts of data.

  2. Is it possible to restrict a web app user’s interaction/view? For example: what if I wanted to restrict "Dave" to only viewing/interacting with the database through the "Research Projects" view, but still allow other users full access….is this possible?

    Thx

  3. Eric,

    One way to do this would be to add an OnLoad UI macro for the forms you want to restrict. You can use the UserEmailAddress() expression to check the current user and allow or restrict the view as appropriate.

    -Andrew

    • Andrew

      Is that feature not active on the preview version? The "On Start Macro" under the Advanced tab is greyed out, and the other two Macro options do not have OnLoad as an option.

      Thx

      PS This may not be the right blog post to ask this, but is it possible to auto populate fields on a Web form based on a ComboBox selection? This was easy in previous versions of Access via the =ComboBox#.column(#) expression, but this does not appear to work in this version (at least within a Web App).

    • Andrew

      After some more digging I found the "On Load" UI selection. Would I have to enter every single email address seperately, or is it possible to restict the view based on the email extension ex: @hotmail.com or @gmail.com

      Thx

      • One idea to make this work would be to create a "users" table where you would store the email addresses and data on the level of permissions assigned to each user. Then, you could access this table with a Data Macro from the On Load UI macro. That way, each page could simply check if the logged-in user had the appropriate permissions–in a general way–rather than typing each email address into the macro.

  4. Andrew

    I am officially lost on how to properly use the UserEmailAddress() expression. I keep getting a parsing error…

    Help…

    • Eric,

      Sorry to hear you’re having trouble–can you tell me more about what error you’re seeing and what the UI macro is that you’re trying to write? One tip that might help: the UserEmailAddress() function is only available in UI macros (not data macros).

      -Andrew

  5. In Access 2010 web databases, one of the really good points was the ability to use Rich Text. Is this possible with Access 2013 web databases, please, or has this been removed?

  6. To clarify my question on Rich Text, I realise that Access 2010 web databases stored the text as, well, text, and it was the UI which did the magic showing and editing colours, underlining, bold and so on, but this does not appear to be possible with an Access 2013 web database.

  7. Question no. 2. I am trying out my Access 2013 web database and using it on my Android 4.0 tablet. No matter whether I use the built-in web browser, Google Chrome or Firefox, there are no scrollbars appearing in the browser. thus I can’t use my app.

    Is it my tablet (with all three apps not working properly) or is there something wrong with the way Access creates the pages?

  8. Andrew,

    can you provide us with a tutorial how to set up data filtering within an Access 2013 Web App? I do not understand how to use the UserEmailAddresĀ­s() function.

    I also manged to connect to a SharePoint list, but when I remove permissions an App user still sees all records. Which user is used for this trusted relationship?

    Best regards,
    Christiaan

  9. Hi Andrew, no matter what user logs in to use the Access App, UserEmailAddress Always contains the same (wrong) address.

  10. Hey Access team, can you guys post how does one create a header-detail kind of form for entry. like order header-order detail. it should look like a single unit form and on save it should save to two different tables. also can we get a blog post on how to restrict based on UserEmailAddress. looks like a lot of us have the same issues.

Comments are closed.