SQL Query with not equal won't run

M

MarkP

Registered User.
Local time
Today, 16:45
Joined
Jun 13, 2003
Messages
15
I have two query's. One for fall semester and one for spring semester. I am trying to set up a transcript. I am getting an error
on the lines with <> but I do not know of any other way to do this in an access database. I have asked several people for help and no one can figure this out. I am getting really frustrated with it. Please help.

SELECT DISTINCT QryFallSemester.ChildID, Transcript.GradeLev,
IIf([QryFallSemester.Subject]=[QrySpringSemester.Subject],[QryFallSemester.Subject]) AS SubjectBothSem,

Iif([QryFallSemester.Subject] <> [QrySpringSemester.Subject] and [QryFallSemester.Subject] is not null, [QryFallSemester.Subject] as SubjectFSem,

Iif([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QrySpringSemester.Subject] is not null, [QrySpringSemester.Subject] as SubjectSSem,

IIf([QryFallSemester.Subject]=[QrySpringSemester.Subject],Trim([QryFallSemester.Gradeentered] & "/" & [QrySpringSemester.Gradeentered])) AS GradeBothSem,

IIf([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QryFallSemester.Subject] is not null, [QryFallSemester.Gradeentered] AS GradeFSem,

IIf([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QrySpringSemester.Subject] is not null, [QrySpringSemester.Gradeentered] AS GradeSSem,

FROM ((QryFallSemester INNER JOIN Child ON QryFallSemester.ChildID = Child.ChildID) INNER JOIN Transcript ON Child.ChildID = Transcript.ChildID) INNER JOIN QrySpringSemester ON Child.ChildID = QrySpringSemester.ChildID
WHERE (((QryFallSemester.ChildID)=[Forms]![transcriptc]![name]) AND ((Transcript.GradeLev)="10")
 
IIF functions have three arguments, you code appears to have only two.
 
I am trying to use nested iif statements so the second option would be the next iif. I am not sure if my () are in the correct place's. Maybe you could help me with that or tell me if I can even do what I am tring to do. Maybe it will help if I break it down a little more.

SELECT DISTINCT QryFallSemester.ChildID, Transcript.GradeLev,
IIf(([QryFallSemester.Subject]=[QrySpringSemester.Subject],[QryFallSemester.Subject]) AS SubjectBothSem,
Iif([QryFallSemester.Subject] <> [QrySpringSemester.Subject] and [QryFallSemester.Subject] is not null, [QryFallSemester.Subject] as SubjectFSem,
Iif([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QrySpringSemester.Subject] is not null, [QrySpringSemester.Subject] as SubjectSSem,,)

IIf(([QryFallSemester.Subject]=[QrySpringSemester.Subject],Trim([QryFallSemester.Gradeentered] & "/" & [QrySpringSemester.Gradeentered]) AS GradeBothSem,
IIf([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QryFallSemester.Subject] is not null, [QryFallSemester.Gradeentered] AS GradeFSem,
IIf([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QrySpringSemester.Subject] is not null, [QrySpringSemester.Gradeentered] AS GradeSSem,,)


FROM ((QryFallSemester INNER JOIN Child ON QryFallSemester.ChildID = Child.ChildID) INNER JOIN Transcript ON Child.ChildID = Transcript.ChildID) INNER JOIN QrySpringSemester ON Child.ChildID = QrySpringSemester.ChildID
 
Last edited:
Sorry, my mistake. I'll take another look at it.
 
Well, I'm not entirely sure what you want will work, but for every '(' you should have a ')' and I'm not sure why you have a ',,)' in some places. It looks like you should use a Select Case statement instead, but it might help if you sat down and wrote something like this:
Code: 
IIf(condition,
  true
,
  IIf(condition,
    true
  ,
    IIf(condition,
      true
    ,
      false
    )
  )
)
This way you can match up a ')' with all your IIf's more easily.
 
You can't use a Nested IIf() to do what you seem to want to do. An IIf() is a function and functions return a single value. You seem to be trying to get the IIf() to return different calculated fields depending on the conditions that you are testing. An IIf() in a query can return only a single calculated field.

You have some concept issues also. Queries return a consistant set of columns. You cannot have row 1 return columns a, b, c and row 2 return d, e, f.
 
Caught you on this one Pat -

I've returned different columns in a query using a conditional if to select the column. :D
 
Nope, Sorry:)
Our poster is trying to return columns that are named differently:
SubjectBothSem or SubjectFSem or SubjectSSem depending on the values in the IIf()'s.

You can do:
IIf(condition, fldA, fldB) As OutField

but you cannot do:
IIf(condition, fldA As OutField1, fldB As OutField2)

And the latter is what MarkP is trying to do.
 
Help

Can you give any suggestions as to how I can accomplish what I am needing to do or is access really as weak as everyone says it is? I have already figured out how to do it in SQL and Cold Fusion but unfortunatley I need access to do it. Every time I ask for help from someone they say acces can't do it.
 
or is access really as weak as everyone says it is
Click to expand...
- a vicious rumer spread by Java :) The rest of us think Access is quite powerful.

Why don't you try to explain in words what you are trying to do because you can't do what it looks to me that you are trying to do with your invalid syntax. And I am certain that you cannot do it with any other relational database either so I wouldn't say that that makes Access weak.
 
Maybe you could supply an example of the output you're wanting.
 
Indeed i agree with pat, reading the SQL i dont think any database system supports this...

There are options available to do what you want but not just that what your proposing....

Depending on what you want to achieve ( an example would be great) many options are feasable....

Regards

The Mailman
 
I am attaching a zip with everything you should need to look at the problem the way I see it. If you need anything else please feel free to ask or make suggestions. I know that the tables are not setup corrctly but to change them I would have to change thousands of lines of cold fusion code.
 

Attachments

I could not get both attachments in one file. This is an example of the end product.
 

Attachments

Here's your original query altered a bit. You just had quite a few missing parentheses. I ran this query with the two Fall and Spring queries open.

SELECT DISTINCT QryFallSemester.ChildID, Transcript.GradeLev,
IIf([QryFallSemester.Subject]=[QrySpringSemester.Subject],[QryFallSemester.Subject]) AS SubjectBothSem,
IIf([QryFallSemester.Subject] <> [QrySpringSemester.Subject] and [QryFallSemester.Subject] is not null, [QryFallSemester.Subject]) AS SubjectFSem,
IIf([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QrySpringSemester.Subject] is not null, [QrySpringSemester.Subject]) AS SubjectSSem, IIf([QryFallSemester.Subject]=[QrySpringSemester.Subject],Trim([QryFallSemester.Gradeentered] & "/" & [QrySpringSemester.Gradeentered])) AS GradeBothSem,
IIf([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QryFallSemester.Subject] is not null, [QryFallSemester.Gradeentered]) AS GradeFSem,
IIf([QryFallSemester.Subject]<>[QrySpringSemester.Subject] and [QrySpringSemester.Subject] is not null, [QrySpringSemester.Gradeentered]) AS GradeSSem
FROM ((QryFallSemester INNER JOIN Child ON QryFallSemester.ChildID = Child.ChildID) INNER JOIN Transcript ON Child.ChildID = Transcript.ChildID)
INNER JOIN QrySpringSemester ON Child.ChildID = QrySpringSemester.ChildID
WHERE (((QryFallSemester.ChildID)=[Forms]![transcriptc]![name]) AND (Transcript.GradeLev)="10");

It looks like this might not be the exact output you're looking for, though.
 
Last edited:
I have been struggling with this for to long.. That did work and thank you. But I guess my logic was not really thier. It's not returning what I wanted. Can anyone please give me some direction. I'm not sure what to do with this. Thier is an example of what I want it to look like in the zip I put up.
 
SELECT DISTINCT Child.ChildID, Transcript.GradeLev, IIf([QryFallSemester.Subject]=[QrySpringSemester.Subject],[QryFallSemester.Subject]) AS SubjectBothSem, IIf([QrySpringSemester.Subject] Is Null And [QryFallSemester.Subject] Is Not Null,[QryFallSemester.Subject]) AS SubjectFSem, IIf([QryFallSemester.Subject] Is Null And [QrySpringSemester.Subject] Is Not Null,[QrySpringSemester.Subject]) AS SubjectSSem, IIf([QryFallSemester.Subject]=[QrySpringSemester.Subject],Trim([QryFallSemester.Gradeentered] & "/" & [QrySpringSemester.Gradeentered])) AS GradeBothSem, IIf([QrySpringSemester.Subject] Is Null And [QryFallSemester.Subject] Is Not Null,[QryFallSemester.Gradeentered]) AS GradeFSem, IIf([QryFallSemester.Subject] Is Null And [QrySpringSemester.Subject] Is Not Null,[QrySpringSemester.Gradeentered]) AS GradeSSem
FROM ((Child INNER JOIN Transcript ON Child.ChildID = Transcript.ChildID) LEFT JOIN QryFallSemester ON (Transcript.ChildID = QryFallSemester.ChildID) AND (Transcript.Subject = QryFallSemester.Subject)) LEFT JOIN QrySpringSemester ON (Transcript.Subject = QrySpringSemester.Subject) AND (Transcript.ChildID = QrySpringSemester.ChildID)
WHERE (((Child.ChildID)=[Forms]![transcriptc]![name]) AND ((Transcript.GradeLev)="10"));

Does that do what you want?

If not please supply me a text file example output of what you want to have based on the files posted on the board. The Sample output you gave shows 2 semester grades for Goegraphy (Geography?) while in the table there is only 1...

Regards

The Mailman
 
You must log in or register to reply here.

Similar threads

K
Method for combining crosstab queries with same criteria from multiple fields?
Replies
2
Views
2,206
keen1598
K

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom