Many to Many Relationship

[AnnPhil]

I have a db that is tracking contracts. Each contract has a primary person (or entity) and could also have several secondary persons attached to the contract. What makes it a many to many relationship is that for every person in the database they could be tied to more than one contract and for every contract there are many people tied to it. So i created three tables and now i want to create a form to enter the data and that is where i am getting stuck!!! Need help!!! Example of tables

Contract table: ContractID (autonumber), date, addition info....
People table: PeopleID (autonumber),name, address......
Contract/People table: Contract/PeopleID (autonumber), ContractID (Number), PeopleID(Number) and Type (text).
The field "Type" is used to define whever the person is a Primary, Secondary, or co-signer for the contract.

I want to create a form to fill in the tables but am running into alot of problems. Any suggestions??

Thanks in advance

 

[Hayley Baxter]

I may be able to help you but may need more information on the problems you are facing. I have completed a database that sounds similar to yours although it is not a many to many relationship. Is the form your trying to create a form that will allow you to add information to all of your tables? ie adding new contract and info on who signed it etc

I can maybe put together a sample form for you. Let me know if this will be helpful.

Hay

 

[AnnPhil]

Yes i need a form that will allow me to enter data about the contract and people associated with the contract. By creating the third table (Contract/People) i now have two one to many relationships which is what they say to do when confronted with a Many to Many relationship, however i can not get the form designed to work properly.

 

[AnnPhil]

I played with the form and figured it out. Thanks anyway to all

 

[AnnPhil]

Thanks Pat for the file. Once i figured out what i was doing wrong i created two forms also. One that show contracts and list of persons associated and another one showing the People and list of contracts associated to them. I also added a button on the second form so as to jump to the first form to view more info about a contract if need. Works great!

 

[music_al]

Many to Many Problem

Hi Pat / Ann

I am creating a database for a friends recruitment agency. I have a very similar problem where I have CANDIDATES (Job Hunters) and I have VACANCIES (Jobs posted by Companies). Obviously a Vacancy can have many candidates and a candidate could have applied for many vacancies.

I have created a table called MATCH which I think should act as an intermediate table but, unlike Pays example, I cant seem to get more than one primary key n this intermediate table. How do I do this ?


Allan

 

[music_al]

Many to Many Problem

Hi Pat / Ann

I am creating a database for a friends recruitment agency. I have a very similar problem where I have CANDIDATES (Job Hunters) and I have VACANCIES (Jobs posted by Companies). Obviously a Vacancy can have many candidates and a candidate could have applied for many vacancies.

I have created a table called MATCH which I think should act as an intermediate table but, unlike Pats example, I cant seem to get more than one primary key in this intermediate table. How do I do this ?


Allan

 

[Rabbie]

Hi Pat / Ann

I am creating a database for a friends recruitment agency. I have a very similar problem where I have CANDIDATES (Job Hunters) and I have VACANCIES (Jobs posted by Companies). Obviously a Vacancy can have many candidates and a candidate could have applied for many vacancies.

I have created a table called MATCH which I think should act as an intermediate table but, unlike Pats example, I cant seem to get more than one primary key in this intermediate table. How do I do this ?


Allan

You are using Match as a junction table so you only need 1 Primary Key although this is not strictly necessary. The links to Candidates and Vacancies should be held as Foreign Keys and be defined as long integers.

Hope this helps

 

[tehNellie]

At a basic level what you are looking to achieve is something along the lines of:

TblCandidate:
Field: ID - Autonumber, pk
Field: Name

TblVacancy:
Field: ID - Autonumber, pk
Field: JobTitle

To join these together you need a Junction Table:
tblCandidateVacancyJunction:
Field:CandidateID
Field:VacancyID
Both these fields can combine into the composite primary key for this table if a candidate shouldn't be able to apply for the same job twice.

You now want a One-To-Many Relationship from tblCandidate.[id] to tblCandidateVacancyJunction.[candidateid]
And a One-To-Many relationship from tblVacancyid.[id] to tblCandidateVacancyJunction.[vacancyID]

At a basic level that is all you need. The junction table needn't hold just the relationship information, you might want to store specific information relating to the applications such as date received, cvapproved or somesuch.

 

[Rabbie]

At a basic level that is all you need. The junction table needn't hold just the relationship information, you might want to store specific information relating to the applications such as date received, cvapproved or somesuch.

I disagree. Specific information about an application should be held in the application table - otherwise the data is not normalised. Cvapproved should be store with the candidate details. The junction table is mapping the many to many relationship between candidates and vacancies so a candidate may by definition have several entries in this table and so might a vacancy. It is never a good idea to store the same info twice in your DB. That way you soon get inconsistencies.

 

[tehNellie]

The junction table effectively is the application table as far as I can see.

It's holding information relating to the Candidates Application to the Vacancy.

maybe CVapproved is a misleading example, I don't know what the technical recruitment term is for when the Vacancy.companyid say "yes we like that guy's CV" but that particular detail seems relevant to the Application rather than the candidate.

 

[Rabbie]

I guess we have read this differently. I was under the impression that a candidate would submit one CV and then be matched against a number of vacancies and so would only be matched if the CV was suitable. Having given this more thought I can see your point for adding some information about this Match to the junction table. ie Date match made etc.

 

[tehNellie]

I think we're saying the same thing in slightly different ways and getting our wires crossed. My cv example was probably not very well or clearly explained as to what I actually meant.

I was basically just trying to illustrate that just because a table could be considered a Junction Table, that needn't be the limit of the scope of the thing.

Calling it Applications and storing information relating to that doesn't stop it being a Junction table (have I just buggered things up again?)

 

[Rabbie]

I think we're saying the same thing in slightly different ways and getting our wires crossed. My cv example was probably not very well or clearly explained as to what I actually meant.

I was basically just trying to illustrate that just because a table could be considered a Junction Table, that needn't be the limit of the scope of the thing.

Calling it Applications and storing information relating to that doesn't stop it being a Junction table (have I just buggered things up again?)

I agree. My first reading was that information not specific to a particular match was being stored in the junction table so normalisation was being lost. If the information is specific to a particular match then of course it should be stored there.

 

[music_al]

Hi All

I just cant get this to behave as I expected it would. I might have to post the database up here so someone can take a look to let me know what Im doing wrong.

I have 3 MAIN Tables...

tblCLIENT (this is a table that lists all the companies that have the vacancies)
tblVACANCY (the vacancies that the clients have)
tblCANDIDATES (the people looking for jobs that are MATCHED to the Vacancies)

I have created a 4th table (tblMATCH) which is the intermediate table similar to that in the Many to Many db that was posted up here.

I ideally want 3 forms for CLIENT, CANDIDATE and VACANCY

In the Client form, I would like a Sub Form to show that Vacancies that the currently viewed CLIENT has. I also want a second sub form that shows the CANDIDATES that have been matched to that VACANCY.

In the VACANCY form, I would like a Sub Form that shows the CANDIDATES that have been matched to that VACANCY.

In the CANDIDATE form, I would like a Sub Form that shows the vacancies that the candidate has been short listed for.

I hope this isnt too confusing.


Allan

 

[music_al]

Here is the file in question

Any help would be greatly appreciated


Allan