Transfering data from one table to another

[ron727]

Hi All, I am new to Access so I apologize if this seems obvious. I am presently in the initial stages of a database and would like to do the following. I have 2 tables one called suppliers with (CompanyName, Adress, Phone, Fax, Website etc...) The other tables is a contacts table (standard contact template table) Now what I want is the following:



I fill the info of one supplier in the table for "Company A" : CompanyName, Adress, Phone, Fax, Website

I then want to add a contact that work for "Company A" in the contacts table. What I want now is that when i enter or select "Company A" in contacts table that the other relevant information i.e (CompanyName, Adress, Phone, Fax, Website etc...) fills in automatically

How do we do this and does that interfere with normalization rules?

RThanks for your help

[CraigDolphin]

Hi Ron,

To answer your last (and most important) question first: Yes. Doing that would interfere with normalization rules.

First, I commend you for at least being aware of normalization. Most folks new to access are blissfully unaware of it and allow that to lead them into a whole mess of headaches down the road.

To the problem at hand:

Aside from junction tables, most tables represent an entity of some kind.

In this case you have a table for the entities called companies.
CompanyID (auto, pk)
CompanyName
CompanyPhone
CompanyFax
CompanyEmail
etc

Now there are also entities called 'Contacts' who are people working for companies.
ContactID (auto,pk)
CompanyID (FK)
ContactFirstName
ContactLastName

...and so forth.

One of the rules of normalization is that there should be no repeating information in columns/fields (aside from foreign key values)

Now, if you add a field containing the CompanyPhone into the contacts table, and you have 3 contacts working for one company, you're going to be repeating exactly the same information in the same field three times, thus denormalizing your table.

Now, here's where we start being careful in our distinctions.

Each company generally has a main phone number. Because the information about the phone number relates to the company overall, it belongs in the company table and nowhere else.

But people within that company may also have a direct phone number, or an extension, which is unique to that person. If so, then this is information about the person/contact, not the company itself. So in this case, you could correctly decide to create another phone field in the Contacts table which will never contain repeating information.

Now the key to bring all this together is a query. You can create a query that uses the information in both tables to show you both the company number, AND and the contact specific number, alongside the company name, contact name etc.

Think of a query like it were a (denormalized) table being calculated/created by the database whenever you need it.

And with regard to data entry, usually this is done through a form. The main form being linked to the parent table (Company) and the subform being bound to the child table (Contacts), with the main form and the subform being linked together by the field that they have in common (CompanyID)


Since this

[ron727]

Hi Graig what you have said makes alot of sense to me thank you for answering so quickly i appreciate it

reagards

Ron