Count

  • Thread starter Thread starter Gurgi
  • Start date Start date
G

Gurgi

Hi,
I have data based on test results. My data is in 3 tables as listed below
(I'll only listed pertinent data for this action):

Table 1: Time
Table 2: QuestionID, Correct, Value
Table 3: QuestionID, QuestionDetail (for sorting purposes)

I need to be able to count how many times each question was missed, display
what answer was inputted and compare that data to 2 instances the test will
be taken (before and after training class). The Correct field indicates
whether the learner got the question right or wrong (I'm only interested in
the wrong values-"no"). The Time field indicates when the learner took the
test and whether it was the pre or post test (AM in time field would indicate
pre & PM in time field would indicate post). The Value field indicates what
answer was chosen. Since there are 2 records each from multiple learners,
there will be duplicate QuestionDetail fields, and I would like them to be
grouped together instead of listed individually. This data is being imported
from another test taking program, so I would like to limit having to alter
the tables as much as possible.

Can anybody help? Thanks!
 
The Time field indicates when the learner took the test and whether it was
the pre or post test (AM in time field would indicate pre & PM in time field
would indicate post)
You are trying to be too cryptic and not providing enough information. How
can your Table 1 indicate 'learner' and time just from a time field.
Does this mean one records immediately following the other or to combine the
two records?

Post actual table and field names with datatype and sample data. Post
example of how you want this data to look in the results.
 
Ok, maybe I wasn't clear enough.
Table 1 has records of learner demographic data like name, department,
supervisor, date/time taken, etc that they input into the test program, but I
don't need all this information for this particular report which is why I
didn't mention it. I don't need to indicate the results by learner, only a
collective count of questions missed by all learners for each of the testing
times-pre and post. The only data I need from Table 1 is the time taken, as
that indicates whether the results are before or after training.

What I mean by "grouped together instead of listed individually" is this:
the records in Table 2 are imported by each question, for each learner, for
each test. So if I have 3 learners taking a 20 question test twice, then
Table 2 will have 120 records (one for each question accessed). When I put
the data together, if each learner missed question 2 on the pre-test, I don't
want it listed 3 times. I would like the question listed once with a total
count of how many misses there were. (Again, I didn't design the tables, the
data is imported from another program this way.)

This is basically what I want:
# Missed on Pre-Test Answer # Missed on Post-Test
Answer
Q1
Q2
Q3
Q4
etc. thru 20 questions

The question list would come from the QuestionDetail field on Table 3, and
since it is alphanumeric it would need to be sorted by the QuestionID field:
either primary key field in Table 3 or foreign key in Table 2. The Correct
field in Table 2 indicates if the question was answered incorrectly by a
value of no. The Time field in Table 1 indicates if it was a pre-test before
training or a post-test after training. Pre-tests will have been taken in
the AM and post-tests will have been taken in the PM. The Value field in
Table 2 indicates what answer the learner chose.

I'm not trying to be cryptic, but these tables have a vast amount of data
imported from the testing software, that it would be chaotic to list all that
each table contains. To simplify things, I was trying to only list the
particular fields and data I need for this report. Thanks.
 
In regards to the previous post, I just realized something that will affect
what I want. In the results example below from my 1st reply, I listed a
column for Answer and indicated this would come from the Value field. If
each question is counted collectively, I won't be able to display this field,
as there would not be just one answer that was counted wrong. For instance,
in a multiple choice question with 4 answer options, there are 3 possible
values that could be counted wrong and each incorrect answer could be
different. I think then I would need 2 (or more) separate queries:
1)
# Missed on Pre-Test # Missed on Post-Test
Q1
Q2
Q3
Q4
etc. thru 20 questions
AND
2)
Count
Q1 Incorrect Answer1
Q1 Incorrect Answer2
Q2 Incorrect Answer1
etc. thru 20 questions/answer combinations

I'm not 100% sure if the 2nd query above is the best way to get what I want.
I'll have to think about that one some more. Basically, in addition to
validating each question by determining how many times it was missed, I also
need to validate it by analyzing the answers given to determine if the same
incorrect answer was given repeatedly. Any suggestions?
 
You have time in table 1 but no correlation to table 2. The tables have to
be related to exctract the information.
You need time (signifies pre/post) to be related to the test tesults.
 
Ok, that makes sense. That might be one of the reasons some of the things
I've tried didn't work. It also might explain another field in the tables
(like I said, the program imported many fields, some with what looks like
just strings of characters). There is a linked field between Table 1 and
Table 2 titled ScoreGuide with a "1 to many" relationship. Each ScoreGuide
is unique to each learner record in Table 1, which includes the Time field.
Does this help?
 
That sounds like it relates the two.
Post sample data with fields names the information is contained.
 
Table 1
ScoreGuid Time
57b15fa4 12:31:08 PM
451310e4 1:36:12 PM
19ce4ff5 6:28:18 AM
bfcb60fb 8:49:39 AM

Table 2
ScoreGuid QuestionID Correct Value
57b15fa4 1 no A
57b15fa4 2 yes True
57b15fa4 3 no B
451310e4 1 yes C
451310e4 2 no False
451310e4 3 no D
19ce4ff5 1 yes C
19ce4ff5 2 yes True
19ce4ff5 3 yes A
bfcb60fb 1 yes C
bfcb60fb 2 no False
bfcb60fb 3 yes A

Table 3
QuestionID QuestionDetail
1 Q1-pen ds
2 Q2-pen use
3 Q3-units/ml
 
These should do it --
Pre-Post --
SELECT Table1.ScoreGuid, Table2.QuestionID, Sum(Abs(IIf([Time]<0.5 And
[Correct]=-1,0,1))) AS [Pre-Test], Sum(Abs(IIf([Time]>0.5 And
[Correct]=-1,0,1))) AS [Post-Test]
FROM (Table1 INNER JOIN Table2 ON Table1.ScoreGuid = Table2.ScoreGuid) INNER
JOIN Table3 ON Table2.QuestionID = Table3.QuestionID
GROUP BY Table1.ScoreGuid, Table2.QuestionID;

Wrong answer count --
SELECT Table2.QuestionID, Table2.Value, Count(Table2.Value) AS CountOfValue
FROM (Table1 INNER JOIN Table2 ON Table1.ScoreGuid = Table2.ScoreGuid) INNER
JOIN Table3 ON Table2.QuestionID = Table3.QuestionID
WHERE (((Table2.Correct)=0))
GROUP BY Table2.QuestionID, Table2.Value;
 
Back
Top