Search & mark the rest if a member is selected

[roberttran52002]

Hi all,

I'm stuck, appreciate your guidance!

I'm doing a project of interviewing Only ONE student in each class of a school. I have a file with the list of all 1000 students (name, gender, email... class) and 10 questions: Q1-Q10. These 1000 students belong to 20 different classes.

I don't know how to automatically mark (compute Mark="a memeber in class already interviewed") for all 1000 cases if any student(s) of a class were interviewed.

Ie.. if student 0058 (class 10B) was interviewed (answered Q1-Q10) then all other students belonging to the same class 10B will be flagged in field Mark="a memeber in class already interviewed" ==> I Do not need to interview them.

another example: if student 0142 (class 6A2) was interviewed (answered Q1-Q10) then all other students belonging to the same class 6A2 will be flagged in field Mark="a memeber in class already interviewed"......==> I Do not need to interview them.

If I'm able to do this, I can just find any of student with field Mark="" (blank) to interview to avoid interviewing more than 1 students in the same class.

Thank you ever so much!

Hop

 

[CEH]

Ie.. if student 0058 (class 10B) was interviewed (answered Q1-Q10) then all other students belonging to the same class 10B will be flagged in field Mark="a memeber in class already interviewed" ==> I Do not need to interview them.

I'm not sure I would mark the student record. If you mark the class "10B" as "Closed" (a checkbox) or whatever you want to call it.. then you could via query filter out all student records that contain "10B" as a class. I would probably also have a checkbox for "Interviewed" This would help if you also wanted to filter the student records by those interviewed...
So how I would do this with an "If....then" statement.
With a form built on a query of the student table and the class table I would put.....
If chkInterviewed = True then
chkClosed = True
This would be on the AfterUpdate event of the checkbox.
Lots of ways to do this.... So this is just one example....

 

[wiklendt]

another way you could have a separate table called, say tblSurvey.

you could have a field in there for class and student, possibly cascaded on a form (frmSUrvey) - so you choose which class you want to participate, and then which student from that class you interviewed.

theoretically, you could then have many surveys over time, and rather than flagging either the individual students or classes, you simply have a table with all this info in it seperately.

 

[roberttran52002]

Thank you all very much for your advices!

Sorry as time & cost constraint, I'm requested to interview only ONE student in a class. Hence I can only manually click or mark "closed/ interviewed" for the very student that I already interviewed. However I want access to help me also mark for all the other students in the same class of the ONE interviewed. If i have to do it manually, it will be very tedious

I will try my best based on what you mentioned but it is still very difficult to me as I'm very new to Access and have very limmited knowledge on this.

Much appreciate if you could elaborate a bit more or give me a case study so that I can learn from it.

Thank you ever so much!

Hop

 

[CEH]

I'll try to explain my method a bit more.... In your table for students you would have a checkbox for "Interviewed" I called "chkInterviewed" in you table to classes you would have a checkbox for "ClassClosed" I called "chkClosed"
Now you have a form... This form is based on a query of the student table and the Class table. In this query you would have the criteria for "chkClosed" as "False" (True is "box is checked" False is "box is unchecked") Thus when you open to view records only those with the "ChkClosed" box unxhecked will show. So the code If chkInterviewed = True then
chkClosed = True will mark the chkClass box when you mark the students record "interviewed" to true (the box checked)
I don't know if that makes it any easier to understand or not.
A third table as mentioned is also a good idea....But you would still need to filter on a checkbox by class since that is the one criteria that stops your need to see further records for students in that class.
The set up kind of depends on you final needs of the database.
The best advice I can give on designing a database or application is "Think ahead, think very, very far ahead"

 

[roberttran52002]

Thank you very much for your patience! I'm trying it out but seems like your method requires 2 separate tables: 1 for class & the other for student list. However the list i have at hand is a combined one, from your suggestion I may have to split it into 2 tables so that the "check" can work.

I wonder if there is anyway out that can work directly from 1 original list. My concern comes from the reason I will have to do for other schools/ categories. One more reason, I'm not going to get the list 1 time but I will be provided with the list in many batches, i will have to update the list several times.

Much appreciate your time & kindness!

Hop

 

[CEH]

Unless One student ONLY has one class..... it should be 2 tables.

 

[roberttran52002]

Thanks a lot...

Based on your advice, now I can import the list into 2 tables in Access and apply your method of marking other members in a class if 1 student of the same class is interviewed. Great great!!!...

However, sorry I face another problem that I'm given the continued lists of the first one & I need to import them to the 2 existing tables (1/ Main - All info without "class" & 2/ table with "class" [link to Main table].

Could highly appreciate advice how to update these 2 linked tables by importing external list [excel].

Thanks a lot!

TH