Multiple Records Sub Report issue with Sharepoint Linked Lists

Question

MS Access 2013
Office 365 Sharepoint Linked Lists

I have two tables in a Desktop Access application that uses Sharepoint Linked lists.

Equpiment
Equipment WorkOrders

I'm trying to create a Report where the user can print the details of a specific WorkOrder, and then in a subreport list All Work Orders associated to that specific Unit.

This sounded simple enough, I created a Query using the WorkOrder and Equipment Table to collect all the data I needed to print for the primary report, with Criteria WHERE WorkOrder.ID = 200

Then I create a second query on the Work Order table for the subreport.  I linked them by the Unit # so the subreport will print a list of all Work Orders for the specific Unit of the Parent Report's.

Now this works fine for a single Record it will print the Work Order and Unit Details of the selected WO, and the subreport will print the entire list of Work Orders.

Where it starts glitching is when I try to print multiple Work Orders eg: Criteria: WHERE WorkOrder.ID = 200 Or 250 Or 261

Now the Master Query works perfectly, when run on it's own it will pull up 3 records (where the ID = 200, 250, 261).

But when I open up the Report it will start with the first Page displaying all details for WorkOrder 200, but then when I navigate to the next page instead of printing off WorkOrder 250 (Like you would expect considering there's only 3 records in the Query, and then the 3rd should be 261), the next record becomes WO 2, then 3,4,5,6,7,8,9,10..etc)  The report prints off the entire Table (except WO ID 1).

Why would this be happening? It doesn't make any sense to me.

Another thing that confuses the situation is I have a development application that uses Imported Local Tables that are identical and the system works perfectly with those tables, it's only with the Linked Tables does it break.

And another thing to note is the Work Order table has an ID field Autonumber, and a WO (string field), the WO field 99% of the time equals the ID field (In some cases it does not), if I use WHERE WO = "200" Or "250" Or "261", this works, but I can't use this field because that field doesn't always equal the ID and isn't always unique

Answer 1

I don't fully follow your post but it seems to say, based on your title, your report behaves differently due to where the tables are located.  Although you also post alot of info regarding the query criteria - so it is not clear if that is the issue as well.....

I would first sanity check the table location issue and resolve that; I have never seen a report change its behavior based on the location of the tables.  you state you have 'a development application' that works fine- - but that implies it is a different file; and while you believe it is absolutely identical - it could differ; this is what needs to be checked.   So you must use the same front end file in 2 different set ups:

back end stored locally on same PC/LAN

back end stored in 365/SharePoint list

If you don't use the same front then you really haven't done this test - - - -

If you resolve the table location issue as not the cause - then it may be data specific, which is more likely.

Answer 2

I don't know why but using SELECT DISTINCT in the query solved the entire problem.

I'm on a time crunch so I didn't have time to dig deep as to why this is the case, but everything works perfectly now when using Distinct.