Results 1 to 10 of 10
  1. #1
    Join Date
    May 2013
    Posts
    7

    Unanswered: Combo box question about linking data in separate columns

    Hello all. I've found many great answers about using a combo box to populate other columns with fixed information. I think my question is a little different but if any of you know of a thread that answers this one then please point me in the right direction.

    So here goes:
    I have a column with a combo box that is for test taking attempts (1st attempt, 2nd attempt, 3rd attempt). I've made the next column with combo boxes that say pass or fail. I want to be able to link a pass or fail with a specific attempt from the first column. So for example, I select 1st attempt, the next column can be selected for fail. 2nd attempt, fail. 3rd attempt, pass. Of course each student would have different passes or fails for each attempt.

    I hope my question is clear.

    Thanks,
    Jen

  2. #2
    Join Date
    May 2013
    Posts
    7
    Hi all. I've been trying to find an answer to this but haven't been successful so far. Does anyone have any tips that may help me? Thanks.

  3. #3
    Join Date
    Jan 2005
    Posts
    146
    Your question is unclear. You have a ComboBox with value choices of 1st attempt, 2nd attempt, 3rd attempt. Is it that you have a second ComboBox with choices pass, fail? And you want to restrict the choices based on what is selected in the first CombBox? Please clarify.

  4. #4
    Join Date
    May 2013
    Posts
    7
    Hello! I'm sorry I wasn't as clear as I wanted to be.
    I am working on a student database. I have a column that is a combo box with values of 1st, 2nd, and 3rd (as in testing attempts). The column after that is a combo box with values of pass or fail.
    Let's say student A failed the first two attempts and passed the third. I want to "tie" the fail to 1st attempt and 2nd attempt, and the pass to the 3rd - for that particular student. That way, when I do queries or make charts, I can select for those attempts and see that pass and fail rates for each particular attempt.
    I hope that is more clear.

  5. #5
    Join Date
    Jan 2005
    Posts
    146
    So are you adding a new record for each attempt? Suc that each record contains field something like StudentID, TestID, Attempt, Result. Read up on using Dcount and aggregate functions. As an example:

    Code:
    3rdAttPassPer = FormatPercent(Dcount("*","tblAttempts","[Attempt]='3rd' And [Result]='Pass') / Dcount(("*","tblAttempts","[Attempt]='3rd'))
    This can be included as the source for an Unbound control in a report or form or can be used in VBA.

  6. #6
    Join Date
    May 2013
    Posts
    7
    Thank you. I will read up on those.

  7. #7
    Join Date
    May 2013
    Posts
    7
    Okay I've read about what you suggested and I don't think it's quite what I'm looking for.

    Here are screen shots of a sample table. Now when I go back to that student's record, I want to see which attempts were passed and which were failed. So when I go to the attempts column, I select 1st and it says he failed; I select 2nd attempt and he failed that one too; I select 3rd attempt and he passed. Yay!

    I want, for each student, to able to do this. I haven't been able to figure out how to get pass or fail "linked" (for lack of a better word) to each attempt. Is there a way to do this without writing a bunch of code for each student?
    Attached Thumbnails Attached Thumbnails 1.jpg   2.jpg   3.jpg   4.jpg   5.jpg  


  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Uh, this data design looks unstable. You are changing the record each time - where are you recording the student's testing history?
    If you are planning this design for a table to hold student data, then this is a subtle breach of normal form. For this scenario, I would use three tables:
    tblStudents
    StudentID, Forename, Surname, etc.

    tblExams
    ExamID, ExamName, etc.

    tblExamSittings
    StudentID, ExamID, SittingDate, Attempt, Result

    This will allow you to show exactly what has happened to each student, without over-writing the previous attempt each time.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Jan 2005
    Posts
    146
    Yes, I agree with weejas. You should have a record of each attempt and the pass/fail status. This is why you are struggling.

  10. #10
    Join Date
    May 2013
    Posts
    7
    Ok, thank you. I will try out the multiple tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •