Auto populate a table with description

[ddewald]

I have a database for contacts with 2 tables. One table (Companies) being a list of companies with "companyID", "companyName", "StreetAddress", etc. The other table (Contacts) is a list of contacts with "ContactID", ContactCompanyID", "ContactCompany", etc.

The tables have a relationship between "CompanyID" and "ContactCompanyID".

Is there a way to auto populate the "ContactCompany" column in the "Contacts" table with info from the "CompanyName" column of the "Company" table?

 

[pbaldy]

Some thoughts here:

Autofill

Generally you should only save the key value in related tables, not the description.

 

[ddewald]

So if I wanted to auto populate multiple fields, the correct way of going about it is by having separate tables for each field? Like a table for just company names, and another for departments and so forth?

 

[plog]

The correct way is to not store data everywhere. There is no "autopopulation" to do.

Once you have the ContactCompanyID for a contact, you do not need all the other data about the company in the same table. Instead, when you need to tie it all together and get any data from the Company table, you use a query and JOIN the 2 tables appropriately. So, Contacts should only have a field for ContactCompanyID and not any other company fields.

I suggest reading up on normalization (https://en.wikipedia.org/wiki/Database_normalization), which is the process of properly structuring your tables.

 

[pbaldy]

So if I wanted to auto populate multiple fields, the correct way of going about it is by having separate tables for each field? Like a table for just company names, and another for departments and so forth?

I suppose it depends on you data. Typically probably yes. You'd have a company table with ID and description, etc. You'd have a departments table with ID, description, etc.

 

[ddewald]

Hmmm ok. I guess I need to reevaluate how I'm going about this. Very new to Access and Queries just confuse me.

 

[pbaldy]

Queries are key to getting the most out of a database, so dive in, the water's fine!

 

[namliam]

Hmmm ok. I guess I need to reevaluate how I'm going about this. Very new to Access and Queries just confuse me.

Before you do anything, and I do mean anything research "normalization"

Database normalization - Wikipedia

en.wikipedia.org

Normalization in DBMS - 1NF, 2NF, 3NF, BCNF, 4NF and 5NF | Studytonight

Learn Normalization in DBMS and how to implement 1NF in DBMS, 2NF in DBMS, BCNF in DBMS, 4NF in DBMS, and 5NF in DBMS with examples.

www.studytonight.com

A properly normalized database structure is 80% of any database !