Condition on data

[mysticalorchid]

Hi all,

I'm a novice Access user, recently have created a database to manage my volunteer group's recruitment data, and have a problem on the condition to data entry.
Usually we would have an application round and an interview round as recruitment process. Obviously we'd just call candidates who passed application screening for an interview, however, I'd like to keep data of all applied candidate. So, how can I design my tables, so that, only when I mark a candidate as "passed" on application, would their data be valid on interview <fields/tables>?

For more detail, I've designed:
Table1 <Applicant>
ApplicantID (primary key)
<etc contact detail fields>
Table2 <Project>
ProjectID <primary key>
<etc other info>
Table3 <Application - one applicant can apply for many projects of us many times)
ApplicationID
ApplicantID (foreign key of table1)
ProjectID (foreign key of table2)
ApplicationScreeningResult (YES/NO)
<other comments/info>
Table4 <Interview - one application just gets one interview instance only, if he/she passes>
InterviewID
ApplicationID (foreign key of table3)
InterviewResults
<Other comments>
Table5 <Volunteer>
VolunteerID
<Activities log and other things>

My questions is: I want that, just when I set [ApplicationScreeningResult] to "yes" then the data of that application (link through ApplicationID) be valid in interview table. I wonder if that is possible or I made a mistake in database design.
Also, since table3 and table4 is on a what-I-called conditional one-to-one relationship, I've thought of merging them into one. Is that violate standard in designing database, as I read Access helpfile, it mentioned that all non-keyed field should be independent, if I merge, does it mean that all data related to interview depends on [ApplicationScreeningResult] field?

The same issue is with table 4 and 5, is there a way that, if they pass interview, then they'll given a VolunteerID?

I've searched google with "condition on data entry" "validation rules"... and so on but haven't found a way out yet.
Sorry for my bad English, I'm not a native speaker.

Regards,

 

[MikeLeBen]

I am under the impression you are not using forms to visualize your data, is that correct?

Opening the tables directly to edit the data is something any decent designer will always recommend against.

If on the other hand I am mistaken, you can simply use a conditional query for your datasheet form to only show accepted applicants.

 

[mysticalorchid]

Thanks for the idea.

Well, I did use forms and has tried with conditional queries, however, if there is no validation/condition in the table at all, isn't the data inputted might be wrongly manipulated/retrieved?

Regards,

 

[MikeLeBen]

Why yes, you said you have a condition field (ApplicationScreeningResult), didn't you?

 

[mysticalorchid]

Yup, because I was believing as I said that without condition on the table side, my data could be wrongly retrieved, so I tried various ways to put a condition on the table

Look like I'm wrong about that, I'll stick with the condition on form side then. Thank you very much.

 

[MikeLeBen]

to be honest if you don't store conditional information on tables, your conditions will only last for as long as the form is opened, so you do need to do it.