Data model for recruitment database with reserve list (3 Viewers)

[mike60smart]

Sorry John but you would never allow duplicate records in Candidates_Tbl

 

[mike60smart]

Would it not be easier to have your Main Form based on Candidate_tbl
with the subform based on vacancyCandidate_tble?

 

[JohnLeeS]

Would it not be easier to have your Main Form based on Candidate_tbl
with the subform based on vacancyCandidate_tble?

You might be right. I'll have a go like that, thanks!

 

[Gasman]

but I don't think there's an issue eventually having duplicate records in Candidates_Tbl in practice.

So if you have to update the record, which one do you choose?

 

[Ken Sheridan]

My ultimate aim is for the user to be able to add the new candidates to the vacancy, and then select from a query (I assume) with merit list candidates from the previous 12 months.

You could change the RowSource of the CandidateID combo box in the subform to:

SELECT
    VacancyCandidate_Tbl.CandidateID,
    [CandidateLastName] & ", " & [CandidateFirstName] AS Candidate
FROM
    Candidate_Tbl
    INNER JOIN (
        Decision_Tbl
        INNER JOIN VacancyCandidate_Tbl ON Decision_Tbl.[DecisionID] = VacancyCandidate_Tbl.[DecisionID]
    ) ON Candidate_Tbl.CandidateID = VacancyCandidate_Tbl.CandidateID
WHERE
    (
        (
            (VacancyCandidate_Tbl.ApplicationDate) >= DateAdd("yyyy", -1, Date())
        )
        AND (
            (Decision_Tbl.Decision) = "Merit List"
            OR (Decision_Tbl.Decision) = "Successful"
        )
    )
UNION
SELECT
    Candidate_Tbl.CandidateID,
    [CandidateLastName] & ", " & [CandidateFirstName] AS Candidate
FROM
    Candidate_Tbl
    LEFT JOIN VacancyCandidate_Tbl ON Candidate_Tbl.CandidateID = VacancyCandidate_Tbl.CandidateID
WHERE
    VacancyCandidate_Tbl.CandidateID IS NULL
ORDER BY
    Candidate;

It's necessary to include 'Successful' in the criteria, as otherwise once a candidae has been identified as succsful they would be excluded from the combo box's list and their row in the subform would appear blank. No data would be lost, just hidden, but it's stll not an acceptable solution. The second part of the UNION opeartion would add any candidates not yest assigned to an application to the list. They could then be selected and marked as 'Merit List' where appropriate.

The one reservation I have is restricting the rows returned in the combo box's list to those marked as merit list or successfule with the last year. In time this would exclude candidates in earlier applications from the list and their rows in the subform would appear blank. It would be better to have a VacancyDate column in the vacancies table so that the above query could be restricted to later than one year before that date rather than the current date returned by the Date() function.

A copy of your file with the above amendments to the form/subform is attached.

 

[plog]

What's your real world workflow?

1. How do vacancies come about being entered in the database? A bunch at a time or just one every so often?
2. How do candidates come about being entered in the database? A bunch at a time or just one every so often?
3. Who enters vacancies and candidates? Same person or different?
4. Who makes the connection between vacancies and candidates? Same person or different?
5. What's the timing of it all? Vacancies then candidates or vice versa? When are the connections made--when you enter vacancies do you add a bunch of candidates? When you enter a candidate do you add them to vacancies?

Perhaps a brief paragraph explaining how the process works today without your database involved.