Display same information on a text box from multiple tables

[macace]

Hello everyone!
I hope somebody can help me with the following problem that I have. I have a
table with manuals information. Then I have several tables with the posible
owners of a manual. One of the posible owners is a company, so I have a table
with all the companies (company name, address1, zip ,etc). Another posible
owner is an internal employee of my company. So I have another table with all
the information for my employees (employee name, address1, zip, etc). Both of
these tables share common fields like address, zip, ect, so I thought about
putting both tables together although I dont wan to do that. What I am trying
to do is create a form that display the manual information and the owner
information but since the info of the owners is on separate tables, I dont
know how to make the text box to display the information coming from eigther
table. In other words, I know how to link a text box to a single table but
not to several tables. My guess is that I have to create a query, but I dont
know where to start. Any ideas?

Thanks

 

[John Welch]

Macace- how about this idea:
I'm assuming that a manual can have only one owner at a time.
You could add two fields to your Manuals table: CompanyOwnerID and
EmployeeOwnerID and populate only one of these fields depending on who owns
the manual. You could set the table properties to not allow data in both
fields:
To do this, set the table level validation rule to: isnull([CompanyOwnerID])
or isnull([EmployeeOwnerID])
(Or maybe at some point you might want to know both the employee and
company??)
Then you could build a query that joined the Manuals table to both the
Company and Employee tables on these two new fields. The joins will have to
be LEFT joins (include all records from Manuals table and only those from
other table where fields match)
Include whatever fields (Address, Zip, etc) from these tables that you want
to show for the owner.
Then in your form, to get the correct info into your textboxes, you could
set their control sources like this: (for the zip, for example)
= iif(isnull([CompanyOwnerID]),[tblEmployees].[Zip],[tblCompanies].[Zip])

Hope this helps
-John

 

[macace]

Thanks John!
That is agreat idea. Thank you so much for your help.

Macace- how about this idea:
I'm assuming that a manual can have only one owner at a time.
You could add two fields to your Manuals table: CompanyOwnerID and
EmployeeOwnerID and populate only one of these fields depending on who owns
the manual. You could set the table properties to not allow data in both
fields:
To do this, set the table level validation rule to: isnull([CompanyOwnerID])
or isnull([EmployeeOwnerID])
(Or maybe at some point you might want to know both the employee and
company??)
Then you could build a query that joined the Manuals table to both the
Company and Employee tables on these two new fields. The joins will have to
be LEFT joins (include all records from Manuals table and only those from
other table where fields match)
Include whatever fields (Address, Zip, etc) from these tables that you want
to show for the owner.
Then in your form, to get the correct info into your textboxes, you could
set their control sources like this: (for the zip, for example)
= iif(isnull([CompanyOwnerID]),[tblEmployees].[Zip],[tblCompanies].[Zip])

Hope this helps
-John

Hello everyone!
I hope somebody can help me with the following problem that I have. I have

[quoted text clipped - 22 lines]

Thanks