Hi Guys, I'm building a stock portfolio tracker and am trying to decide the best approach for building a few tables and connecting to my data entry form.
Issue 1: Table1 will have stock codes, but often between exchanges, different companies will have the same stock code.
Issue 2: Table2 will have company names, many which will have different stock codes, allowing for their derivative components.
Issue 3: Form1 is my data entry form, where I select or input a stock code, but I want another field to auto-populate it's associated company name, possibly also its associated exchange in a third field.
How best do I achieve this in constructing my tables? I'm suspecting many-to-many relationships have to be mapped, but what about the auto-populating of other fields. I can build three tables of unique codes, names and exchanges, that's no problem, it's the joining and auto-populating that's got me questioning the methodology.
Sorry if this might be a complicated question. Would anyone have any suggestion approaches to this. Thanks.