Modifying templates and linking to new tables

[gshock]

I'm using the Sales funnel template in Access, and I want to add a new field "Company". I already have a Client_List database. So instead of duplicating, I want to link to that Company field in the existing list. I have already linked it in my current Sales funnel database, and it shows up as an Unassigned Object. I created a new row in the Opportunities Details form, but I can't figure out how to link the new Combo Box to the Company field.

If I have the same company name show up for more than one client, do I have to run a query on the Company field in the Client_List table to remove the duplicates?

I guess, maybe a better question is, can I create a combo box list from the values in another field, in another table?

 

[Micron]

my original table has the same company name repeated multiple times, but different contact names at the same company. For example, Company A is listed three times, because John Smith, Jane Doe, and Joe Blitzflick all work for the same company, but each has different contact information.

That design is not correct. A table is for an entity (companies, customers, whatever); fields are attributes of the company and nothing else. There should only be one record for any company. Repeating values should be handled by a junction table (e.g. tblContacts):

tblCompany

CompanyIDPk

CompanyName

Address

etc

1​

Acme

2​

Ford

3​

Amazon

tblContactsPk

ContactID

CompanyFk

Fname

Lname

Cell

email

etc.

1​

1​

Frank

2​

1​

Bob

3​

2​

May

4​

2​

Ellen

5​

3​

Mark

6​

3​

Joe

If you want to maintain a history of contacts, add a field like ObsoleteDate to hold the date that a contact ceased to be. Fk stand for foreign key BTW, which must be a long integer data type to be able to link to the autonumber id primary fields.

Create accdb file from it and modify the accdb

I imagine you took care of that when you did a save as (which I mentioned you should do in post 9)

I am trying to take the Excel contact list that someone else created and build both a Contact List and an Opportunity List in Access

Knowing Excel is most certainly going to be a problem if you design db tables like spreadsheets. You really need to understand db normalization if you want to keep more of your hair. I suggest you make use of the links I posted here

New to Access - Build a Project Tracking Sheet

Hello all, I am very new to Access. I just switched to decaf so I graciously ask for your patience. I am very strong with excel but this project I am working on may be better off if it was driven through Access as I am really struggling trying to get the final reports, so I am asking for some...

www.mrexcel.com

 

[gshock]

That design is not correct. A table is for an entity (companies, customers, whatever); fields are attributes of the company and nothing else. There should only be one record for any company. Repeating values should be handled by a junction table (e.g. tblContacts):

tblCompany

CompanyIDPk

CompanyName

Address

etc

1​

Acme

2​

Ford

3​

Amazon

tblContactsPk

ContactID

CompanyFk

Fname

Lname

Cell

email

etc.

1​

1​

Frank

2​

1​

Bob

3​

2​

May

4​

2​

Ellen

5​

3​

Mark

6​

3​

Joe

If you want to maintain a history of contacts, add a field like ObsoleteDate to hold the date that a contact ceased to be. Fk stand for foreign key BTW, which must be a long integer data type to be able to link to the autonumber id primary fields.


I imagine you took care of that when you did a save as (which I mentioned you should do in post 9)

Knowing Excel is most certainly going to be a problem if you design db tables like spreadsheets. You really need to understand db normalization if you want to keep more of your hair. I suggest you make use of the links I posted here

New to Access - Build a Project Tracking Sheet

Hello all, I am very new to Access. I just switched to decaf so I graciously ask for your patience. I am very strong with excel but this project I am working on may be better off if it was driven through Access as I am really struggling trying to get the final reports, so I am asking for some...

www.mrexcel.com

Agreed. I know it's not correct, but it's just how the table was formatted when it was given to me. I do understand the need to normalize, but I'm not well practiced at doing it. To that point, I have a lot of questions about how the FK works. Above, you stated that,

must be a long integer data type to be able to link to the autonumber id primary fields

Which fields are you referring to? The Primary fields in the tblContactsPk, or the CompanyDPk? I have been coached in previous exmaples using Qlik where we loaded a field from one table, combined with a field from another table to create a new KEY that would link the two tables. For example, instead of using the CompanyFk, we might load something like this in the SELECT statement: CompanyName & ContactID As CompanyContactForeignKey.

When I am creating new entries in the tblContactsPk form, how does the Fk get updated when I add a new company in the tblCompanyDPk?

Each record in tblContactsPk has a unique ID. Isn't using the Foreign Key the same as using the company name because the values can be repeated, they are just different data types, correct?

When linking the tables, or creating References, do you link the tblCompanyDPk to the CompanyFk? Why does it not work if I link CompanyName and CompanyName in two different tables? I tried doing this, and it didn't work as intended. But that doesn't mean I didn't make other mistakes that obscured any potential benefit.

Thanks for all of your help. Much appreciated.

 

[Micron]

Not sure if this is what you mean: an autonumber is a long integer, primary key. That field gets linked (in queries) to the related field in another table so both must be long integer data type. Follow the normalization links I put in the linked thread and see if the way they explain normalization helps.

When I am creating new entries in the tblContactsPk form, how does the Fk get updated when I add a new company in the tblCompanyDPk?

When creating you're not updating, you're appending. Important distinction. The answer is with your tblCompany main form/subform setup. You would not be allowed to create subform contacts if the main form has no record if you set up relationships for those tables. So create company first, then contact details on the subform, or if adding a contact to existing company, simply add to the subform records.

Isn't using the Foreign Key the same as using the company name

Yes and no. If the company changes its name you have to be concerned with propagating that change throughout your whole db. If you change the name in your company table, everywhere the name is used gets the new name because you used the foreign key of 12 (or whatever) instead of the real value. You can manage this with relationships if done properly but IMO the other method is used by most developers.

Why does it not work if I link CompanyName and CompanyName in two different tables?

"It didn't work as intended" means what? It can work if done correctly. It can also fail to perform as expected with respect to updating queries as you've discovered. Your mistake was trying to get individual names from the related (many) side and at the same time, update a query field based on what became a non-updatable query.

Do follow the links. They will save you a lot of future grief.

 

[gshock]

Not sure if this is what you mean: an autonumber is a long integer, primary key. That field gets linked (in queries) to the related field in another table so both must be long integer data type. Follow the normalization links I put in the linked thread and see if the way they explain normalization helps.

When creating you're not updating, you're appending. Important distinction. The answer is with your tblCompany main form/subform setup. You would not be allowed to create subform contacts if the main form has no record if you set up relationships for those tables. So create company first, then contact details on the subform, or if adding a contact to existing company, simply add to the subform records.

Yes and no. If the company changes its name you have to be concerned with propagating that change throughout your whole db. If you change the name in your company table, everywhere the name is used gets the new name because you used the foreign key of 12 (or whatever) instead of the real value. You can manage this with relationships if done properly but IMO the other method is used by most developers.

"It didn't work as intended" means what? It can work if done correctly. It can also fail to perform as expected with respect to updating queries as you've discovered. Your mistake was trying to get individual names from the related (many) side and at the same time, update a query field based on what became a non-updatable query.

Do follow the links. They will save you a lot of future grief.

Links followed, and greatly appreciated. I can't say that I already new everything before reading them, but I did understand a LOT of it before. I'll start a new thread, because now my questions are more about creating the primary and foreign keys, and using them properly.