If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Relationships
I am creating a DB for a recruitment agency, so it will hold all our clients
records and all the candidates that have worked for them. I have a Clients form which combines fields from 2 tables Clients tbl and Candidates tbl as a subform on the second page (tab control). When I go to complete the second page of the form, I get an error message saying basically I need to complete the information in the Candidates tbl before I can amend/create this record. I realise it is a referential integrity issue I have but after thinking about it so long I am not to sure if I am in a relationship or not never mind the DB! I think it is a one to many relationship but because of the error I am getting I think it could be a many to many. One client can have many Candidates, some times the same Candidate more than once but a Candidate can also work for more than one client so is it one to many or is it many to many? Or should I leave it as one to many but drop the ref integ? Probably really stupid but help is much appreciated. Thanks |
#2
|
|||
|
|||
Relationships
Lisa
It sounds like you are saying "one Client can have many Candidates" and "one Candidate can be associated with many Clients". If that's an accurate statement, you need THREE tables, not two, because you have a "many-to-many" relationship. Your tables, in that case, might be something like: tblClient ClientID (client-specific info) tblCandidate CandidateID (candidate-specific info) trelClientCandidate ClientCandidateID ClientID (serves as a foreign key to point back to tblClient) CandidateID (serves as a foreign key to point back to tblCandidate) (other info specific to this combination) That third table lists valid combinations of client and candidate. You'll want to first get the data modeled appropriately, then start work on forms to display it... Regards Jeff Boyce Microsoft Office/Access MVP "Lisa Cowan" wrote in message ... I am creating a DB for a recruitment agency, so it will hold all our clients records and all the candidates that have worked for them. I have a Clients form which combines fields from 2 tables Clients tbl and Candidates tbl as a subform on the second page (tab control). When I go to complete the second page of the form, I get an error message saying basically I need to complete the information in the Candidates tbl before I can amend/create this record. I realise it is a referential integrity issue I have but after thinking about it so long I am not to sure if I am in a relationship or not never mind the DB! I think it is a one to many relationship but because of the error I am getting I think it could be a many to many. One client can have many Candidates, some times the same Candidate more than once but a Candidate can also work for more than one client so is it one to many or is it many to many? Or should I leave it as one to many but drop the ref integ? Probably really stupid but help is much appreciated. Thanks |
#3
|
|||
|
|||
Relationships
That is great Jeff thanks. Just one question has been generated by your
answer - if the same Candidate comes up twice with the same client (they worked for them more than once) will that throw up a PK duplication? thanks Lisa "Jeff Boyce" wrote: Lisa It sounds like you are saying "one Client can have many Candidates" and "one Candidate can be associated with many Clients". If that's an accurate statement, you need THREE tables, not two, because you have a "many-to-many" relationship. Your tables, in that case, might be something like: tblClient ClientID (client-specific info) tblCandidate CandidateID (candidate-specific info) trelClientCandidate ClientCandidateID ClientID (serves as a foreign key to point back to tblClient) CandidateID (serves as a foreign key to point back to tblCandidate) (other info specific to this combination) That third table lists valid combinations of client and candidate. You'll want to first get the data modeled appropriately, then start work on forms to display it... Regards Jeff Boyce Microsoft Office/Access MVP "Lisa Cowan" wrote in message ... I am creating a DB for a recruitment agency, so it will hold all our clients records and all the candidates that have worked for them. I have a Clients form which combines fields from 2 tables Clients tbl and Candidates tbl as a subform on the second page (tab control). When I go to complete the second page of the form, I get an error message saying basically I need to complete the information in the Candidates tbl before I can amend/create this record. I realise it is a referential integrity issue I have but after thinking about it so long I am not to sure if I am in a relationship or not never mind the DB! I think it is a one to many relationship but because of the error I am getting I think it could be a many to many. One client can have many Candidates, some times the same Candidate more than once but a Candidate can also work for more than one client so is it one to many or is it many to many? Or should I leave it as one to many but drop the ref integ? Probably really stupid but help is much appreciated. Thanks |
#4
|
|||
|
|||
Relationships
Lisa
If you are saying that it is a valid situation for the same Candidate to associate with the same Client, then you need some additional information in that THIRD table that lets you differentiate ... perhaps the date of association? Regards Jeff Boyce Microsoft Office/Access MVP "Lisa Cowan" wrote in message ... That is great Jeff thanks. Just one question has been generated by your answer - if the same Candidate comes up twice with the same client (they worked for them more than once) will that throw up a PK duplication? thanks Lisa "Jeff Boyce" wrote: Lisa It sounds like you are saying "one Client can have many Candidates" and "one Candidate can be associated with many Clients". If that's an accurate statement, you need THREE tables, not two, because you have a "many-to-many" relationship. Your tables, in that case, might be something like: tblClient ClientID (client-specific info) tblCandidate CandidateID (candidate-specific info) trelClientCandidate ClientCandidateID ClientID (serves as a foreign key to point back to tblClient) CandidateID (serves as a foreign key to point back to tblCandidate) (other info specific to this combination) That third table lists valid combinations of client and candidate. You'll want to first get the data modeled appropriately, then start work on forms to display it... Regards Jeff Boyce Microsoft Office/Access MVP "Lisa Cowan" wrote in message ... I am creating a DB for a recruitment agency, so it will hold all our clients records and all the candidates that have worked for them. I have a Clients form which combines fields from 2 tables Clients tbl and Candidates tbl as a subform on the second page (tab control). When I go to complete the second page of the form, I get an error message saying basically I need to complete the information in the Candidates tbl before I can amend/create this record. I realise it is a referential integrity issue I have but after thinking about it so long I am not to sure if I am in a relationship or not never mind the DB! I think it is a one to many relationship but because of the error I am getting I think it could be a many to many. One client can have many Candidates, some times the same Candidate more than once but a Candidate can also work for more than one client so is it one to many or is it many to many? Or should I leave it as one to many but drop the ref integ? Probably really stupid but help is much appreciated. Thanks |
Thread Tools | |
Display Modes | |
|
|