This doesn't seem like a difficult thing but I can't seem to make it work properly - any help would be appreciated.
I have 3 tables: People, Organizations, Locations.
During data entry I want the data entry steps to follow this flow:
Define person, choose Organization (from lookup) and then choose Organization's location (also via lookup, sometimes there is more than one location , so I want the default location to be location 1, or option to choose a different location, already entered in the Location table).
So: Enter person data, choose Organization 1 with Org Location 1.
OR Enter person data, choose Organization 1 with Org Location 2.
Person table has a match field for Org_ID, and Person table also has a match field for Org_LocationID.
Person's relationship to Organization is 1:1. Organization's relationship to Location is 1:M.
I'm sure I will need to offer more information so please let me know what questions you have.