Three databases means three tables or three files?
What is the relationship that links master to the people and company tables? What are the match fields that you use?
They are three files and are connected by a unique serial number assigned to each record and the type of record (person or company).
But which table gets the unique serial number that the other two match to?
Are these one to one relationships (One and only one record in one table matches to one and only one record in the other table) or are some one to many?
And exactly which table links to which? Do Company and Persons both link to Master?
the Master is the one that generates the unique serial number. Yes, the serial number is unique to one record only so when I am in Master database and click new company a new serial number is generated with the type "company" that also appears in the Company database. There are three because the Master only needs pertinent information to my team while the other two carry specific information about the Person or Company. I want the status field to appear on the Master database but don't want two separate fields.
That doesn't really answer all of my questions. Making some educated guesses, it would appear that you have these relationships.
Master::__pkMasterID = People::_fkMasterID
Master::__pkMasterID = Company::_fkMasterID
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Please confirm whether or not that is correct. Chances are that the names are different, but please pay careful attention to the function of the match fields I am specifying and those you have.
If this is correct, there is a major issue with how you expect a status field in Master to evaluate. The problem, is that you could have many records in People and Many records in Company that all link to the same record in Master. Thus you could have 5 records in People with different values in their status field and 2 records in Company also with different values in their status field. But you want to somehow combine the data from all of these possible records into a single field and there is more than one way to combine that data...
Sorry, I'm trying to answer your questions as best as possible. I think I understand your notations and believe you are correct in the relationships but I'm not understanding the problem- they do not link to the same record in Masters. The record number never repeats so record 001 is a Person and only appear in the People Database and Masters as a person, record 002 is a Company and appears in the Company database and Masters as a company, etc. Record 002 does not appear in the People database- the type restricts that from happening. So if I'm displaying record 001, a person, in the Master database, I want the value of status field from the People database but when I click over to the next record, 002, a company, I need that status from the Company database to populate the field. I hope that makes sense.
I figured there were several ways to do this but the easiest please.
I'm sorry but I don't understand your description.
If you have a record with __pkMasterID = 1 in Master, you could, theoretically, have 500 records in people all with _fkMasterID = 1. That's because the value is uniquely generated in Master but copied to People every time you link a record in People to Master. This is also the case for the relationship that I guessed at for Master to company.
I don't know if this is the case for your tables and relationships or not.
Two ways you can help clarify:
Upload a screen shot of Manage | Database | Relationships cropped to just the Tutorial: What are Table Occurrences? for these three tables.
Write up a 2 paragraph or so "narrative" explaining how each of these tables are supposed function in terms of what you see on the screen. Explaining the purpose of this "status" field and the values that might be entered into it could be very helpful.