Access query returns extra and duplicate rows when OR criteria values aren't in ascending order - SharePoint-linked tables only

Massimo Giannuzzi 0 Reputation points
2026-09-21T13:47:34.8166667+00:00

Summary: I have a query joining two SharePoint-linked tables, one row expected per match. The row count and content depend only on the order I write the OR'd values in the WHERE clause - ascending order always returns the correct rows, any other order returns extra rows for values I never asked for, plus duplicates of rows I did ask for. No error is raised either way.

Environment: [Access version and build]. Tables linked to SharePoint Online.

Confirmed local, not linked: the same data copied to local Access tables - by changing the foreign key field from text to Number in place, round-tripping through Excel, and a Make Table query off the linked tables - returns correct results every time, regardless of criterion order. The fault only reproduces against the SharePoint-linked tables.

Example 1: tblCustomers (one) INNER JOIN tblCustomerBoats (many) on CustomerID

SELECT tblCustomers.CustomerID, tblCustomerBoats.CustomerBoatID

FROM tblCustomers INNER JOIN tblCustomerBoats

  ON tblCustomers.CustomerID = tblCustomerBoats.CustomerID

WHERE tblCustomers.CustomerID = <value1> OR tblCustomers.CustomerID = <value2> [OR ...];

Data: customer 1 has one boat (353), customer 2 has two boats (1765, 441), customer 3 has one boat (480), customer 4 has one boat (689), customer 5 has one boat (135).

1, 3 -> 2 rows (expected 2). Correct.

3, 1 -> 5 rows (expected 2). Extra: customer 2. Duplicate: customer 3.

1, 2, 5 -> 4 rows (expected 4). Correct.

1, 3, 5 -> 3 rows (expected 3). Correct.

1, 4, 5 -> 3 rows (expected 3). Correct.

5, 2, 1 -> 9 rows (expected 4). Extra: customers 3, 4. Duplicate: customers 5, 2.

5, 3, 1 -> 8 rows (expected 4). Extra: customers 2, 4. Duplicate: customers 5, 3.

5, 4, 1 -> 8 rows (expected 4). Extra: customers 2, 3. Duplicate: customers 5, 4.

5, 4, 2 -> 9 rows (expected 4). Extra: customer 3. Duplicate: customers 5, 4, 2.

4, 3, 2 -> 8 rows (expected 3). No extras. Duplicate: customers 4, 3, 2.

5, 4, 3, 2, 1 -> 11 rows (expected 5). No extras. Duplicate: customers 5, 4, 3, 2.

2, 1, 3, 4, 5 -> 8 rows (expected 5). No extras. Duplicate: customer 2.

 

Example 2: tblLookupBoatStorageLocations (one) INNER JOIN tblBoats (many) on StorageLocationID, at production scale

Ascending order (1, 5) returns the correct 140 rows: 127 boats at location 1, 13 at location 5. Reversing the order (5, 1) returns 302 rows: the 13 boats at location 5 (correct), then the entire boat lists for locations 2, 3 and 4 (91 + 29 + 29 = 149 boats, none of which were in the criteria), then the 13 location-5 boats again (duplicated), then the 127 location-1 boats (correct, not duplicated). 13 + 91 + 29 + 29 + 13 + 127 = 302.

The pattern, across both examples:

Criteria written in strictly ascending order always return the correct rows.

Any other order returns extra rows for every value that falls between two criteria values written out of order relative to each other, even though those values were never in the criteria. In example 2 this leaked in three entire, unrequested categories (locations 2, 3 and 4), not just stray rows.

Where the criteria values are contiguous with nothing in between (e.g. 4, 3, 2), no extras appear, but duplication still does.

Most criteria values that are followed by a smaller value later in the list get duplicated. The exception seems tied to whether the sequence ends at the true lowest ID/value that exists in the table (not duplicated, as with 5, 4, 1 and 5, 1) or stops short of it with a lower value still existing (duplicated anyway, as with 5, 4, 2). I can't confirm the internal cause of that from outside the query engine; I'm reporting it as observed.

Workaround: filtering on the joined ("many" side) table's own copy of the field, rather than the parent table's field, has returned correct results in every case tried, regardless of value order.

Questions:

Has anyone else reproduced this, specifically against SharePoint-linked tables?

Is this a known limitation of how Access plans or caches a query against a linked SharePoint list when OR criteria aren't sorted?

Is filtering on the joined table's field a dependable general workaround, or could it fail under some other condition?

 Also posted: https://www.access-programmers.co.uk/forums/threads/access-query-returns-extra-and-duplicate-rows-when-or-criteria-values-arent-in-ascending-order-sharepoint-linked-tables-only.335777/

Microsoft 365 and Office | Access | For business | Other
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jayden-P 3,130 Reputation points Independent Advisor
    2026-09-21T15:27:24.4766667+00:00

    Hi @Massimo Giannuzzi

    I attempted to reproduce the behavior using SharePoint-linked lists in Access, including testing the same query with the OR criteria written in different orders. In my testing, reversing the order of the criteria did not change the results, and I was unable to reproduce the extra or duplicate rows you described.

    User's image

    User's image

    Could you please confirm the following?

    • Whether the issue occurs for all SharePoint-linked tables or only specific lists.
    • Whether the behavior can be reproduced in a new blank Access database linked to the same SharePoint lists.
    • Does replacing the OR conditions with an IN() clause produce the same behavior? For example:
      WHERE CustomerID IN (5,1)

    At this time, I have not found any Microsoft documentation indicating that OR criteria must be written in ascending order when querying SharePoint-linked tables, nor any documentation describing this as a known limitation of Access or SharePoint-linked list queries.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.