Table Relationship Primary Key to Multiple Fields

[Bluecider]

Well.... I'm quite embarrassed. I used to be pretty good at MS Access, but put it down for a number of years and now I've found I've forgotten some basic stuff... So please forgive this type of question.

What I'm trying to do is keep data integrity between two tables, but with a catch. Maybe this is not possible, so appreciate alternative ideas.

TABLE 1 = COMPANIES
Company Name
Company Type ('Type 1' or 'Type 2' or 'Type 3')

TABLE 2 = JOBS
Company Name 1 (Type 1 Company)
Company Name 2 (Type 2 Company)
Company Name 3 (Type 3 Company)

Basically, I want to register a company in TABLE 1, and define the TYPE of that company.
A Company can only have one Company Type.
Depending on the Company Type, will determine if I can populate(choose) the company in TABLE 2's 'Company 1,2,3' fields.

So for example, each JOB in TABLE 2 can have up to 3 COMPANIES. (A different company in each Company 1,2,3 fields).
For example, the company that can be selected in the field 'Company Name 1' can only be a company with the appropriate 'Company Type' (as defined in TABLE 1). 'Company Name 1 can only be a Company defined in TABLE 1 with a 'Type 1'.

Now, let's say I want to change the Company Name for a Type 1 company, I want that change to update down to all JOBS that contain that company based in Company Name 1. But it can't just happen for only Companies with 'Type 1', but any Company Type I change. If the Company Name is a Type 2 Company, then it will change all Company Name 2 in TABLE 2 and so on...

So how to create a relationship between TABLE 1 <> TABLE 2 where any changes in TABLE 1 will propagate down to each Company Name # field in TABLE 2 depending on the Type defined in TABLE 1?

**I know I can create a query in TABLE 2 for each Company Name # field to pull companies that equal a certain type.
Yet, if I change the name of the company in TABLE 1, how is that change propagated down to all records in TABLE 2?

 

[Bluecider]

Ok. Noone?

I think I got it working anyways. In [DATABASE TOOLS] in [Relationships], I just duplicated TABLE 1 3 times and linked each primary key to each Company Type field in TABLE 2.
So, There are 3 types a companies, and there's a special field in TABLE 2 for each type.

 

[HiTechCoach]

Yet, if I change the name of the company in TABLE 1, how is that change propagated down to all records in TABLE 2?

That should never happen if you follow the Best Practices for a well design relational.

Every table should have a primary key that is automatically assigned (not based on user input) and will never change. In access this is simple with a field set to Auto-number data type.

In table 2 you would store only the primary key from table one. When you need the Company Name you look it up from table one using the primary key

 

[Bluecider]

Thanks for your reply, and I agree.

It's hard to explain exactly what I'm trying to do, so let me be more specific:

There are TWO tables: JOBS and COMPANIES

For each JOB, there may be up to 3 DIFFERENT companies based on COMPANY TYPE defined by a field in the company record in COMPANIES
So, for example, JOB #1 may have a COMPANY called 'Bank of Earth' which is company type of 'Company'
As well, there may be a recruitment firm for JOB #1 as well. There's a separate field in JOBS table to specify the Recruitment Company' that's representing the job for the Company.
The Recruitment Company COMPANY TYPE is 'Recruitment Agency', and the 'Recruitment Company' field in the JOBS table will pull all companies from the COMPANIES table where Company Type = 'Recruitment Agency'.

So, one JOB Record in the JOB table may have more than 1 company record coming from the COMPANIES table. As there are three fields in the JOB table now (Company, Recruitment Company, Consulting Company)

So, how to keep relational integrity for all three fields from the COMPANIES table. That was what I was tying to do.

 

[xenou]

Having three repeating fields (CompanyType1, CompanyType2, CompanyType3) is a violation of the rules of normalization. I'm not sure you're even in first or second normal form if you do that. So don't worry about the "right way" if that's how you do it - it's just wrong to begin with. Your solution might work, at least in practice - but it's just not going to provide the benefits of normalized databases is all (queries may be difficult to write, it may be hard to maintain, and you will run into certain types of problems that could affect your future ability to update or develop the database).

The Jobs table probably should be structured:
JobID, JobType, CompanyID

Example:
1, "Recruitment", Company1
2, "Consulting", Company2

And since CompanyType is already an attribute of the company, you could avoid even that:

1, Company1
2, Company2

The real question I think you are asking is how to avoid assigning two companies of the same type to a job, or (possibly) how to avoid having more than 3 companies on a job.

 

[xenou]

One question - how many company types exist in your companies - only three or more than three?

 

[Bluecider]

One question - how many company types exist in your companies - only three or more than three?

3 Types

A field for each type in the JOBS table (3 total fields)

 

[xenou]

There's a few ways I could think of doing this and none are particularly wonderful for a simple access setup. If what you are doing works I'd stick with it. Just beware that if you ever want to add a fourth type, you're in trouble, as you will actually have to change table structure (add a new field) and probably change all the queries, reports, and forms that deal with this data (also adding a new field to each of them). So if you aren't 99% sure that you won't be adding a new type at some point, then this probably won't be good.

Note that one of the ways of doing this is to just handle all the checks and condition logic in your UI. Basically, using a form with validation checks to make sure that you don't have duplicate types or too many companies. If the validation fails, you just abort the changes. The only thing is that you must be sure that you always use your form for changes, and don't have people going in and changing table data directly in tables. In that case, you simply have JobID, CompanyID in your table, and everything else is in your data entry / data changes form.

 

[Bluecider]

Yeah, it will be locked down. And yeah, I understand that I can't have any more company types. That's ok. I really don't foresee it, and if it happens, I'll make the update.
Entry of data to the table will be controlled on the form, but since each field already has a condition to pull the proper company type in the drop down selection, that's not an issue for me.
Company type will be a mandatory field in the company table and only those three types can be selected.