Table structure question...

[Kyp]

Working with a vendor table.
There can be many different vendor companies we order from...
Within the vendor company, there can be different contacts according to department.
How do you structure a table accounting for this scenario?

Example:
Company XYZ has several different locations throughout the country.
Each location is unique therefore, when ordering from company XYZ, regardless of location, depending on type of product I'm ordering, the vendor contact can be different.

I was thinking tbl1 for the company name, and associated information and tbl2 for the company contact containing contact information.

If that is even remotely correct, how do I join a vendor company with the vendor contact?

 

[The_Doc_Man]

You should look up something called a JUNCTION TABLE - which you can find using our SEARCH function.

Here is the thumbnail explanation:

You have what appears to be a many-to-many situation: Many vendors, many locations (and even many departments).

You make a table of vendors. You make a table of locations. You make a table of departments. The "departments" can be a child table, one location having many departments. But here is where the junction table comes in. You make a table that contains CONTRACT info and in it, you include a link of one contract to one of the departments AND a link of that same contract to one of the vendors. What this does is it makes the contract table an intermediary between many departments and many vendors.

This is a total of FOUR tables

Location ==>> Department ==>> Contract << == Vendor

 

[theDBguy]

Hi. If one contact can only be associated with one company, you may be able to use a one-to-many relationship. That is, if you disassociate the company with location and simply associate location with the contact. Just a thought...

 

[Kyp]

Wow...
Any way to keep it simple?

 

[theDBguy]

Wow...
Any way to keep it simple?

Are you asking Doc or me?

 

[The_Doc_Man]

Beware of the "keep it simple" question. The REAL question is to keep it FUNCTIONAL and EFFICIENT. What I just showed you was a normalized solution for the problem as you described it. Actually, it IS simple once you get used to using SQL and JOIN queries. But like anything worth doing, it is worth doing well. Actually, that isn't so bad a layout. When I did one of my major security action trackers for the Navy, I had three tables and two junction tables plus about a dozen "translator/lookup" tables because the Navy ALWAYS uses codes for everything.

 

[GPGeorge]

Wow...
Any way to keep it simple?

A bicycle is simple in comparison to a dump truck, but you can't carry a lot of rocks in the basket on your bicycle's handlebar basket....

 

[plog]

You explained your situation 3 times, each a little different. You introduced 4 different entities in different parts of your explanation--vendors, contacts, locations and departments. In setting up tables usually each entity becomes its own table. In the screenshot example you provided you had just 2 of those--Vendor and contacts.

Does that work for you? From a normalization standpoint its correct--it adheres to the rules of setting up tables. Doesn't mean it will work for you and your data, but you did it correctly.

You need to decide if locations and departments are important and need to be there own tables within your database. It comes down to how many of them are related to how many of other entities. Here's some questions to help:

Can a location have multiple contacts?
Can a department have multiple contacts?
Do those 2 tables you posted capture all the data you will need?

From what I see, I think those 2 tables are all you need, unless you have more data that go to location and department than you have show.

 

[Kyp]

Forgive my frustration...
I am trying to learn this as I go and want to do it correctly. This is not something I have to do, it's something I WANT to do.
Again, sorry for my frustration.

-Kyp

 

[LarryE]

Your design is fine. Just change the name of the TblVendorContact to TblVendorLocation. Then each vendor could have multiple locations and each location has a contact and each contact processes your Purchase Orders and ships products. Not complicated at all.

 

[Pat Hartman]

Step back and tell us how you know what "contact" you need. Instead of using the tblVendorCompany as the relationship to the order, use the tblVendorCompanyContact. Then the other table would only be used for consolidated reporting.

 

[Kyp]

Step back and tell us how you know what "contact" you need. Instead of using the tblVendorCompany as the relationship to the order, use the tblVendorCompanyContact. Then the other table would only be used for consolidated reporting.

Good morning Pat.
Thanks for replying to this thread.

I've taken a step back and reverted to pencil and paper to try and get the tables & relationships figured out.

Synopsis:
Two tables. tblVendorCompany, and tblVendorContact.
Within some companies that I order materials from have different locations as well as different departments.
A vendor company has many departments. For each type of component, a different department with a different sales person. If I want to purchase PLC components from this company, I will contact "John Doe", in the PLC department. If I want to purchase ControlNet devices from the same company, I would contact "Jane Doe" in the ControlNet department but Jane is at a different geographical location, same company.

My thought was not to repeat any data that I already have so I did not want to list the same company name 10 times for 10 different contacts in the table.

Maybe I'm incorrect in my thinking, but that's where I'm at....

 

[jdraw]

@Kyp
Just trying to clarify the business involved.
It seems you are more concerned with Contacts and Locations than Product. Your posts tend to talk about Products/Parts being supplied from different departments of the Vendor company. My read is that you're buying Products/Parts and (from your purchase history) you would know which department supplied/sold certain Products/Parts.
Somewhere in your scheme there should be Product/Part ( or some categorization) that relates to Department. As an aside, companies may change personnel, so a contact can change independent of your purchasing. It is the department where your contact works/worked that is important when you're ordering Product/Parts.
Good luck.

 

[LarryE]

Good morning Pat.
Thanks for replying to this thread.

I've taken a step back and reverted to pencil and paper to try and get the tables & relationships figured out.

Synopsis:
Two tables. tblVendorCompany, and tblVendorContact.
Within some companies that I order materials from have different locations as well as different departments.
A vendor company has many departments. For each type of component, a different department with a different sales person. If I want to purchase PLC components from this company, I will contact "John Doe", in the PLC department. If I want to purchase ControlNet devices from the same company, I would contact "Jane Doe" in the ControlNet department but Jane is at a different geographical location, same company.

My thought was not to repeat any data that I already have so I did not want to list the same company name 10 times for 10 different contacts in the table.

Maybe I'm incorrect in my thinking, but that's where I'm at....

You are correct in your thinking. The next table you need is the purchase order table linked to the tblVendorContact table. So, create a TblPurchaseOrders table with a PurchaseOrderID as a Primary key and VendorContactID as a foreign key. Of course, you will have VendorContactID as a Primary key in the tblVendorContact. Link the two fields together with Referencial Ingegrity enforced.

So each VendorContact can then process your Purchase Orders for you. You are on the right tack, just keep going.

 

[Pat Hartman]

I understand the relationship between vendor and contact but linking to a vendor doesn't help you with tracking which contact you used. That is why I recommended linking to the contact instead. OR, you can link to both using cascading combos. The first combo is vendor and it controls the contents of the second combo which will be contact.

 

[Kyp]

The intention is to select from a combo box the company, then in a cascading combo box select the contact needed in relation to the company name from the company combo box. Text boxes under the contact combo box would populate with the selected contact.

This would happen in an order form of course but I am nowhere close to that point yet.

I only have the 2 tables so far. There would be many more to come... (If I can learn this)
My approach was one task at the time starting with the vendors.

I still have to figure out the orders table, and the accounting stuff which involves a business area, company number, cost codes, cost center number and description and so forth.

Knowing almost nothing, I have a LONG road ahead to learning how all this works

 

[mike60smart]

Hi Kyp

You would need the following 2 tables for the Cascading

tblCompany
-CompanyID - Autonumber PK
-Companyname

tblCompanyContacts
-CompanyContactID - Autonumber - PK
-CompanyID - Number - LongInteger
-ContactFirstname
-ContactLastname


You would then create a Form to allow you to manage the Company Names & Company Contacts.

The next Step would be the tables for managing an Order.

 

[Pat Hartman]

Here are two samples Look at the next to last Item in the first one. The second example is all about using cascading combos in a subform.

Autofill and other examples

This database shows examples of how autofill works. It also shows examples of filtering with a multi-select listbox. I added an updated version of the database with additional examples.

www.access-programmers.co.uk

Cascading Combos in continuous form

This is an example that shows how to work with cascading combos in a continuous form. This technique will NOT work for a form in datasheet view. Here's an update.

www.access-programmers.co.uk

 

[Kyp]

@Pat Hartman
Following your example, I've put together a form that list the company, and the contact within the company selected.
Works well but I have a question...

Having only two tables, tblVenCompany, tblVenContact.
What is the best approach to add a new company to the tblVenCompany?
The table originally had two columns of data. Now, only one, [Company].
Should I create a form for this? Wanted to use the "Not in list" event to handle it.

 

[Pat Hartman]

Personally, I hate allowing people to add data to a lookup table on the fly. It just encourages typos. But the feature is there and you have the ability to control it since the NotInList event uses a form that you make, so do what you want. I would make the form though so you can give them better search features than just the RowSource of the combo.
I'm surprised there isn't more data for the company but if there is no central address or contact info, I guess everything but the name would be in the contact table.