Relationships

[Lisa Cowan]

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

 

[Jeff Boyce]

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]

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]

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