You are being infinitely patient with me and I appreciate it so much.
Everything that is in my query is what I've posted -nothing more. I think
the problem is in the way I'm understanding the query process and the point
of my confusion. I understand that the class1 query will return a result of
anyone who got question 30 incorrect, regardless of what the topic is. I
thought that Access would go through the first expression (class1), return
the result and then, move on to the next expression (Class2) and return that
result, etc. etc. Since each of the results comes out on its own line, it
made sense to me that way. For instance:
Doe Jane Footnotes
Doe Jane Paragraph numbering
Doe Jane Styles
So it appears from the result that each expression is being run separately.
Then, when it gets to Document Formatting, I get the results in another
column, but then the results appear again right next to "Footnotes", like
this:
Doe Jane Footnoes Document
Formattiing
Paragraph numbering
Styles
Document Formatting
So, it appears to my untrained eye that the document formatting expression
is being run at the same time as the footnotes expression, and then again on
its own. None of the other expressions are doing this even though, according
to the logic you are telling me is at work, I should be getting multiple
results in all of these expressions.
I'm only pounding away at this because I think if I can understand the whole
concept of queries and their logic, that I will go a long way to
understanding Access. Thank you so much.
John Nurick said:
Don't forget that I can't see your data structure or your data, and I
don't know - because you haven't told us - whether you are using any
criteria on your query to restrict the records it returns. Remember that
the criteria in the IIF() expressions in the calculated fields will be
applied to every record in the table _unless_ you use criteria in the
query proper too.
So "Class1" doesn't only pick people who answered Question 30
incorrectly. The expression returns "Footnotes" in those records for
which [QNum] is 30 and [Final] is "Incorrect", regardless of what
[Topic] may be.
Likewise, "Class5" returns "Document Formatting" in those records for
which [Topic] is "Editing" or "Formatting" AND [Level1]="INT" AND
[Final]="Incorrect" - for question 30 or any other.
So if you have a record where [QNUM]=30 and [Topic] is "Editing" or
"Formatting" and [Level1] is "INT" and [Final] is "Incorrect", it fits
your criteria for _both_ Class1 and Class5.
Thank you very much. But this takes me back to my original question which is
that I thought each of these queries ran separately. Somehow the criteria
for Class5 and Class1 are overlapping. Class1 should only pick people who
answered question 30 incorrectly. It should have nothing to do with whether
or not the topic is "editing" or "formatting". That's in a totally separate
expression, which is Class 5. Does this make sense?
:
Presumably there's at least one record which meets both your Class 1
criteria (and therefore returns "Footnotes") and your Class 5 criteria
(and therefore returns "Document formatting").
Something like this should find it/them (assuming John Spencer got the
simplification right<g>). Create a new query, switch to SQL view, and
paste this in:
SELECT * FROM [XXX]
WHERE ([QnAllTable].[QNUM]=30)
AND ([QnAllTable].[Final]="Incorrect")
AND ([Topic] IN ("Editing","Formatting"))
AND ([Level1]="INT")
;
You'll need to replace [XXX] with the actual name of your table; I guess
it's QnAllTable but I'm not sure.
On Thu, 19 Jan 2006 10:50:02 -0800, Joanne
Thank you very much. But why does "document formatting" come out twice?
It's only being queried once.
:
Hi Joanne,
In a simple Select query like this, the query engine in effect makes one
pass through the records that meet your criteria. Each calculated field
is evaluated independently in each record - which means evaluating each
argument to each IIF().
To me, this means that Access *does* look at them all as separate IIF()
expressions. Your question "Does Access not look at these as separate
'IIF' statements?" therefore makes me wonder if I've understood your
explanation properly.
On Thu, 19 Jan 2006 08:04:02 -0800, Joanne
Hello,
I thought that if I put six different expressions in a query, that Access
would go through the table data six times and essentially run six different
queries. But in the expressions below there is an overlap between Class1 and
Class5 because Question #30 [QNUM]=30 could also have a topic of "editing"
and a level of "INT". I'm guessing that's why, in the result the word
"Footnotes" prints as it should but on the same line I get "Document
Formatting". Then I get "Document Formatting" again. Does Access not look at
these as separate "IIF" statements?
I hope this is clear and thanks in advance for your help.
Class1:
IIf([QnAllTable]![QNUM]=30,IIf([QnAllTable]![Final]="Incorrect","Footnotes",Null),Null)
Class2: IIf([QNUM]=9 Or [QNUM]=25 Or
[QNUM]=37,IIf([Final]="Incorrect","Print Formatting",Null),Null)
Class3:
IIf([Topic]="Formatting",IIf([Level1]="ADV",IIf([Final]="Correct","Styles",Null),Null))
Class4: IIf([Topic]="Editing" Or
[Topic]="Formatting",IIf([Level1]="ADV",IIf([Final]="Incorrect","Paragraph
Formatting",Null),Null))
Class5: IIf([Topic]="Editing" Or
[Topic]="Formatting",IIf([Level1]="INT",IIf([Final]="Incorrect","Document
Formatting",Null),Null))
Class6: IIf([Topic]="Editing" Or
[Topic]="Formatting",IIf([Level1]="BEG",IIf([Final]="Incorrect","Word
Formatting",Null),Null))
