Data model for recruitment database with reserve list (1 Viewer)

[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.

 

[JohnLeeS]

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.

Fantastic, thank you Ken. Really appreciate all that

 

[JohnLeeS]

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.

Currently there is a team of admin staff who records recruitment data in a rudimentary excel spreadsheet. Each row of the spreadsheet is a vacancy, and there are single columns for all the candidates associated with that vacancy, those who were successful, those who were hired. So multiple people recorded in a single column.

Each admin is allocated a vacancy, which they then manage for its duration. The first thing that is recorded is the details of the vacancy (role etc.) when it goes live. When all the candidates are known, the same admin will enter that information (into a single column). Then noting the successful candidates and so on.

The main problem re: translating this to a database, is that candidates unsuccessful for a given vacancy can be added to a reserve list for 12 months. They can then be chosen to fill a later vacancy. At the moment this is just a matter of filling in another single column of names. Ideally I need a fairly fool proof way of managing this on forms in Access.

Incidentally, the above seems a bit ropey, but this process doesn't manage the actual vacancies. That sits with a third party solution. However, it's not possible to get reports, do any data analysis with this solution. Hence why a more robust local database has been requested.

I hope that makes sense