I have 2 DBs:
An SQL Server DB called Concepts.
An Access DB called Initiatives.
I have read-access to Concepts DB and full-access to Initiatives DB.
The idea is each Concept will belong to only one Initiative.
So, one Initiative can have multiple concepts. (1 to Many relationship)
The Concepts DB right now only maintains Concepts information and the concepts have not yet been linked to any Initiatives. New Initiatives have to be created and concepts have to be grouped under them.
When i do a search for concepts from the Concepts DB, i want to only view Concepts with / without InitiativeID.
When i do a search for concepts from Initiatives DB, i should be able to not only view Concepts, but also Link / Unlink them to a New / Existing Initiative.
Do i need to create a Lookup tables in each DB and also have Write-access to Lookup table in Concepts DB?
What will be the fields in both the Lookup tables in order to do a cross-lookup between the two DBs?
When i create a new Initiative in Initiatives DB and search and link concepts under it, will that be automatically updated in Concepts DB, so that when searching from Concepts DB, i should see the updated concepts with the InitiativeID to which they belong?
Lastly, how do i link a group of Concepts to fall under a particular Initiative? What is the Query for it?
Can someone help me with this problem?
P.S : I have included a rough diagram of the tables which i am sure someone will point out as incorrect relationships.