A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Display same information on a text box from multiple tables



 
 
Thread Tools Display Modes
  #1  
Old December 22nd, 2005, 05:07 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Display same information on a text box from multiple tables

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
  #2  
Old December 22nd, 2005, 08:20 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Display same information on a text box from multiple tables

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" u16900@uwe wrote in message news:5938c07983e1c@uwe...
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



  #3  
Old December 22nd, 2005, 08:47 PM posted to microsoft.public.access.forms
external usenet poster
 
Posts: n/a
Default Display same information on a text box from multiple tables

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

John Welch wrote:
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Word applies direct format on File open Uriel General Discussion 16 November 27th, 2005 08:22 PM
Creating dynamic cross reference links in a Word document torajudo General Discussion 4 October 25th, 2005 03:51 PM
Need Subforms? AccessRookie Using Forms 7 April 8th, 2005 09:30 AM
Change font of part of text John Powerpoint 7 March 15th, 2005 11:10 AM
Concatenatd fields in a query for a searching form Marc Running & Setting Up Queries 8 October 19th, 2004 08:49 PM


All times are GMT +1. The time now is 12:11 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.