You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Tips
How-to
News
Videos
Stories
(This post was first published in August 2011 and is the first in a series of posts about using query criteria, which includes Query Criteria Part 2: Like what? and Query Criteria Part 3: prompting for input by using a parameter.)
Queries are a fundamental part of developing and using any database. In addition to providing a way to make bulk changes to your data, they are how you ask questions about that data. Query criteria help you make your question more specific - so instead of asking "What are my contacts' birthdays?" you could ask "Whose birthdays are coming up next month?"
Let's look at these questions in Access. The first question is a very simple query of a single table (Contacts). It just asks Access to select the name and date of birth of everyone in the table:
When I run it, I get the full list:
Very basic stuff. In this particular example, I don't have a huge list to look at, but imagine if I had hundreds of contacts. I probably wouldn't be interested in looking up all their birthdays very often. Now let's see how I could use a criterion to ask the more useful question: whose birthdays are this month?
Here's the query design again, only this time I've added an expression in the Criteria row of the DOB field:
Now when I run the query, I just see folks whose birthdays are this month:
You can read more about how query criteria work and get a comprehensive set of of examples in the article Examples of query criteria. If you liked this example but want to see something a bit more involved, check out this YouTube video about showing only those contacts whose birthday is today.
Next up in the series: using LIKE and wildcards in query criteria.
--Steven Thomas
Comments: (16) Collapse
I absolutely agree with the premise that we should be specific in our request. However, I want to point out that sometime our request to filter may have adverse impact on performance, especially for a large table. In the example, we had this for Criteria:
"Month([DOB]) = Month(Date())"
This actually forces a table scan because the query is not "sargable". If we add an index on column "DOB", there's no way for the engine to compare the index (which contains full dates) against the expression "Month(Date())" and to figure out whether a row has Month value of 8, it has to evaluate each row to do this. This would be actually more optimized:
"DOB BETWEEN FirstDayOfMonth(Month(Date()) AND LastDayOfMonth(Month(Date())"
This expression, admittedly more lengthy, and requires two user-defined VBA functions, enables Access database engine to optimize the request and use the index on DOB directly, which means faster execution time.
It'd be nice if Access was capable of giving feedback on whether a query is optimized as it could be so user could be at least alerted that the criteria they're writing may have detrimental effect on performance, or maybe even support functional indexes, enabling much easier comparsion of "=Month(Date())". Until then, developers really need to ensure that their queries are not just filtering to what they want but also that the queries are sargable.
You're ahead of the curve, Ben! Thanks for the great comment. You are correct that the criteria example I used is not performance-optimized. It's a learning example for people who aren't familiar with query criteria and what they can do.
That said, the example would perform acceptably well for many small businesses, who aren't likely to have enough records in their Contacts table to run into serious lag scanning the table. Moreover, it's simple and straightforward, and will work in a web database.
The performance angle would make a great future post in this series - thanks for bringing it up!
--Steven
Sorry to hijack this thread (as I did the SP1 thread), but partly after being encouraged by a post here some time ago to use Office365 to host Access 2010 web databases, I signed up for its beta, then 7 weeks ago I started paying for the service. However, even though it is no longer in beta and I am paying for its use, web database reports in a web UI still do not work. I have asked several times on the Office365 community site and was told some time back that the Microsoft people there had no information about when it would go live. On this blog Steven Thomas suggested I keep on asking on the Office365 site, but no-one is answering there any more despite several subsequent requests for information. So, to be blunt, this is getting to be a bit of a farce. Would someone at Microsoft please tell us when we will be getting the service we are paying for. If I bought a car, I would not expect to pay for it if one of the wheels is missing. Neither do I expect to pay for a service with one of its wheels missing.
Alan, I appreciate that you are frustrated.
Per the Access product team: “Today, Office 365 does not support Access Services reports. You can view the report by opening it in the Access client. We welcome your feedback on Access Services and Office 365 as we explore future plans for the service."
Someone should be answering you on the O365 forums. I've received assurances that they are in the process of doing so.
Hiya Steven,
Thanks for getting back. Any chance you could give your colleagues a nudge, please? There is nothing new on there since 9th July. community.office365.com/.../25496.aspx is the thread I was using.
If Microsoft are not going to make this basic functionality live quickly, I may ask for a refund.
Does anyone know of another provider who meets EU data-protection rules?
Steven,
Nothing seems to have been posted over at Office365 yet. I have came across a post by Roger Jennings at accessindepth.blogspot.com/.../comparing-characteristics-of-on.html from March 2011 which mentions that Office365 would not have Access reporting enabled when Office365 was launched so some problem was known about 5 months ago. Surely someone at Microsoft can tell us what is happening.
Hello Alan,
I have said what is going on - there are no current plans to support in-browser Access reports in Office 365, but the product team is considering whether to add it. I don't have any more detailed information. I can't speculate as I am an employee and it would be inappropriate, but I think that the reasons may be obvious if you think about it.
I've contacted a different person who may be able to get some movement on the forums. I wouldn't expect to hear anything different there unless something changes, but perhaps the unanswered questions could be handled. Thanks again for your help.
-Steven
OK, thanks Steve. I had not realised "there are no current plans to support in-browser Access reports in Office 365". Thanks for clarifying this.
Sorry to have harped on about this. There was no information coming from the Office365 people and I appreciate you answering the question (even if the answer was not the one wanted).
Alan I am happy to help you get the information you need to make a decision - I absolutely understand your position. You weren't harping, you were being persistent. I hope you continue to follow the Access blog - it takes commenters to make a community really work!
In case it will help the case for making Access reporting live in Office365, may I bring to your attention the following information (as I understand it):
• Access web databases widen Access’s reach considerably, but do have some weaknesses, e.g. a lack of action and union queries and, relevant here, the ability to group and summarize data unless in a report. The use of data macros to try to do anything other than the bare minimum of summation is not feasible. Access web databases need all the help they can get in this area. Office365’s emasculation of their functionality is a major hindrance, not a help
• If a user is using a web UI with a published Access web database it may well mean they do not have Access itself on their machine (why would they use a web UI if they have program itself?). Thus the suggestion at office.microsoft.com/.../build-and-publish-an-access-database-to-sharepoint-HA102435342.aspx to open a report from a database hosted in Office365 is not realistic. Users will not usually have access to Access itself.
• The EU has relatively strict controls on the transfer of personal data, e.g. Directive 95/46/EC, and thus if data is moved outside the EU it must either be into a country with appropriate legal data protection, e.g. Canada or Argentina (but NOT the USA) or the organisation involved must have obtained permission under the Safe Harbor rules. Microsoft have done this so Office365 is OK as far as I know. Currently I am aware of no hosted SharePoint 2010 options meeting these requirements other than Office365.
• Thus EU organisations wishing to use Access web databases seem to have no ability to use hosted Access web databases which are fully functional and which meet legal requirements. Thus such organisations will be reluctant to use Access web databases.
Wow! Great feedback, Alan. We really appreciate the thoughtful consideration.
I am not sure if this is the place for this question. I have started using the "Issues" webdatabase provided by MS as a template against the latest Enterprise Sharepoint edition.
I am faced with a very serious performance problem with queries. when I use the search function in the "Issues" database on the sharepoint GUI against oh about 1400 records, it takes 3-4 minutes to get a result back. The same query when I open in a local (client) copy of Issues that is online with sharepoint it runs in a couple of seconds, can someone help me in finding what is wrong with sharepoint and or access services that this query is taking that long to run.
The whole sharepoint web databases are unusable if the search is not working on the Sharepoint GUI as that is where the application gets used. Our IT staff is stumped as to how tof ix this performance problem with the query. I mean its awesome when it is on client but its real slow on sharepoint side.
Hi Faisal - this is a fine place for your question; thanks for asking it! Let me do a little research for you and I'll post back here.
Hello Faisal. The first thing that comes up is the possibility you should add some server capacity. Have a look at this Technet article about capacity planning for Access Services: technet.microsoft.com/.../gg440600.aspx
There may be other issues involved and I may need more information about your database. However, based on the content of that TechNet article I figured I should get it to you ASAP. I'm still looking at other possibilities and will roll those up in one more comment.
Comments: (loading) Collapse