Your Search Returned 60832 Records The Maximum Is 6000 Records Narrow Your Query and Try Again
OK you should not have any difficulty filtering the larger tables. However there's a couple of got ya and hiccups along the way that you'll have to resolve before this will work.
First of all if you're dealing with a query that joins in other tables, as a "test", simply remove all those other tables. And try filtering on the simple query that now only has one table involved.
You should find that filter works. If the query still fails, then try filtering on an indexed column. In fact what the problem is, if you're using hosting such as office 365, if you upload a table with more than 5000 records, and then attempt to add an index to a particular column, the system will come back and say the table is too large for this operation.
In a way this is kind of a bug, because what happens when you use the publishing wizard and upload a table, if it has more than 5000 records, the records are uploaded first, and THEN Access attempts to set the index. The result is that those error messages that SharePoint spits out NEVER are seen by you. The result is your index settings are IGNORED for when you upload tables with more than 5000 records.
The only workaround I know is to make the table smaller than 5000 records, set up the correct indexing, and you should be off to the races.
You don't mention if the query involved has a join, but it means that when joining columns in that query you MUST have indexing on the columns used in the join (specifically I'm talking about the foreign key column).
It is a simple matter to bring up a table in layout view, and highlight the particular column in question and check in the ribbon if in fact there is an index on that column.
You will see something like this:
Note how in the above I clicked on a column called EventID, and note the indexed check box. If your table is over 5000 records, then you will NOT be able to change that setting. (so you cannot remove it if it already set for 5000+ tables). As noted, this is really much kind of a bug during upload.
So, for testing, try a filter on a known column like the PK (ID) column. Also, if you have a query with joins, check the above index column, and again for testing remove any additional tables in the query (joins) that are not indexed. For the above table, I been able to create a query like this:
Note in the above, I also display the number of records in each table (so, contacts has 7923, and Events has 5983).
Also note "careful" how I put a blue box AROUND the foreign keys (and also have the blue box covering the left join I built - these are the ones that need indexing, and if any are larger than 5000 records, then your query will fail unless they are indexed).
So, when I upsized the above, I could not make ANY query work that joined in ANY of the tables above that had more than 5000 records. When I checked the indexing, that was the culprit.
Also, note that the above limitation only applies to office 365, or perhaps other SharePoint hosting. You can change and modify these limitations if this is your own SharePoint server. So, for example, max of 50 (or is it 40) columns that can be returned with 365 quires. For local SharePoint, I change the default to 255. Same goes for max number of quires allowed in a join etc.
So, you can work with larger tables, but if you use a query and join on columns without index, then the queries will fail.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Source: https://social.msdn.microsoft.com/Forums/office/en-US/90c24c11-7846-433b-b532-4e0739c66786/access-services-and-sharepoint-list-query-limits?forum=accessdev