Hi friends,

 I am having an DB contain the following datas as main source data Name of the Company , name of the equipment , date of purchase , address of the company .

 Table 1 : Name of the Company , name of the equipment , date
            of purchase , address of the company .

 Table 2 : Name of the Company , Address of the company.

 The Table2 is supportting to Table1 i.e name of the company in table1 is of combo box and the source data of the combo box is from table 2 .

  what i want to know is is it possible for to do as follow that is when i sellect the name of the company the address of the company should fill in the address of the company datatable automatically.

 pls give me the clarifaciton as soon as possible.If ur not able understand the pls give me an reply .

 need help immedate.

 thanks in advance .

bye,
pap raguv

Hi,

   It sounds to me like Table 2 stores information on the Company, including its address, and Table 1 is a transaction table that gets the Company name from Table 2 in the combo box.

   The issue is, why do you want to store the address of the company in Table 1? You don't need it stored a second time. This is not normalized data. If you need the address of the company, use a query to display it. You should not have to continually store this data in Table 1.

   What are you really trying to do with the Company address from Table 1? Do you need it in a report or a label, or for some other purpose? If so, we can help you get the data when you need it rather than storing the same data over and over and over. This is very inefficient.

HTH.


mmcdonal

i understand,
 that fact is i dont want to update the address of company for every record that i entering each & every time i.e i want keep the address of the companies in common , there are 20 to 30 companies in the list and the company names in the list may repeat many time so i dont want to select the name & address of the company by searching in the comba box when they appear.

P.A.P.Raguv

Now I am confused. I am not sure what you want to do.

You want to store the address of the company in Table 1 when you select the company from Table 2, is that correct? And you want to do this because you have many companies with the same name, and the only way to differentiate them is by their address? Adn you don't want to look up their address in the combo box in Table 1 when you view the data to see which company of the same name you are looking at?

If that is the case, I would suggest a second field in Table 2 that is more unique and identifies each individual company, and use that field for your combo box, and not the company name.

Alternatively, you can do it with a subform on your data entry form, and not have to store it over and over.

Just create a subform from Table 2, then format it with no border, and flatten all the fields, and then lock them. Then drop the form on your main form, and link the child/parent fields on the PK/FK fields. Then when you select a company, its address will appear on your main form, and no data will be duplicated. I use this technique to identify users by showing their full name. Set the background colors of the main form and subform the same and the wording will just appear on the main form, and not look like a subform.

HTH

mmcdonal

Storing the company address in two tables is unnecessary and bad database design, don't do it. Use a query to link the information if you need it for something.

We covered that issue. The idea is that the user can see which of several companies with the same name they are dealing with without having to refer to the address field in Table 2. This can be done as you say with a query/subform on the main form that is formatted to look like part of the main form.



mmcdonal

He guys,

 i think ur not able to understand my problem or why ur saying that use table2is unnecessary . First a fall i want to clear our guy about my probs ..

 The table 1 is having an from as said above and the table 2 is kept as data source i.e. the name and address of the companies are stored in the table 2 and when i select the company name in the from the adress of the company should show in the from and store in the data sheet of table 1..
 did u understand if u feel that this is unnecessary pls pls explain me y its is

 bye,
 papraguv

If the names of the company are unique in table2 (that is no company name has more than one address), then that is your primary key for table2. Since that is the index, it is the only piece of data required to refer to an element of table2 in any other table (it would be considered a foreign key in table1). Duplicating data such as the address unnecessarily increases the size of the database, as well as creates more dependencies if you have to update data shared between tables later as it has to be updated in more than one place.

In reality, there are very few times you should be using tables to show data from multiple sources. If you think of something as a noun, it should be in a table of its own (i.e. company, equipment, purchase, etc.). For example, your database could be:

Table1 - Company
Name (PK)
Address
Phone
Point of Contact
...

Table2 - Equipment
Item Number (PK)
Name
Price per Unit
...

Table3 - Orders
Order Number (PK)
Company (FK)
Item Number (FK)
Quantity

Where PK is a Primary Key, and FK is a Foreign Key referring to the Primary Key of another table. That minimizes duplication, and you can use join queries to pull together the data you want to show in a form or report. Hope that helped.

Hi,
   I understand what u say, i am having the simillar table that wat u said above but a small correction , asume that the table3 is in the from view the fact is when iam updating the data in the form i have to enter the Ordernumber , so and so .. in that what i want is when i give the name of the company ., adress of the company should automatically appear in the list box of the form (.i.e. instead of typing the address ) did u understand? i want to know wat i have to do for that .weather i have to creat any Query ot or any relation . pls explain me...

Create your form using the query:

SELECT *
FROM (Tbl3 INNER JOIN Tbl1 ON tbl3.Company = Tbl1.Name) INNER JOIN Tbl2 ON Tbl3.ItemNumber = Tbl2.ItemNumber

Then your form will automatically pull in any data you want displayed as long as you enter the Company Name in the Company field of Tbl3.