Separate tables for each location would appear to be the first mistake.
Use one table for all three locations but with a field that identifies the location. You can then set up reports that show the orders broken down by location using this field rather than three separate tables. This simplifies your data model to just two related tables instead of 4.
What you show should work, however. It just doesn't really look like an ideal design. 3 Portals on the Order_Cups layout should show all records from the related tables. Though I would use a single field in Order_Cups in all three relationships.
In the case of any relationship, the records only match if the two fields used as match fields have the same exact values.
I agree that this would be much easier if it was all on one table, however this is not my full database. Each location/table has multiple things they are inventorying (cups, sugar, sweet n low, creamers, stir sticks, equal, etc) and each table is referencing the previous record to populate a "Previous" table. If there is a better way to set this up then I am all ears because I can't seem to get this to work the way I thought it would.
What you describe would not convince me that separate tables make sense. There are ways to get data from the "previous record of the same location" using relationships that match to a different occurrence of the same table that includes the location field as part of the match. Matching to the previous record with the same type of object as well as location is also possible.
And in my last post, I also commented on ways to get your current design to work if you insist on keeping it.
That sounds way better! I will look into that option rather than the mess I have created already. Thanks for your guidance.