Subquery with multiple criteria

[beckyr]

Wondering if someone could help me with a subquery (i only learned they existed today) Is it possible to have multiple criteria in them? For instance i want a TU_CODE where they're in a certain faculty, tu_only is false AND where the number of students they already have (gotten by me trying to do a subquery) is not over the chamber size AND another subquery where none of that tutors entries in tblPreference are for a certain course?? Here's what i have so far, and all it gives me is a syntax error

strsql2 = "Select tblTutor.TU_CODE " _
& "From tblTutor " _
& "Where tblTutor.[TU_FAC_NO] = " & fac & " AND tblTutor.[TU_ONLY] = false AND (Select tblStudents.STU_TU_CODE from tblStudents where tblStudents.TU_CODE = tblTutor.TU_CODE AND ([tblTutor.TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)))) " _
& "AND WHERE NOT EXISTS (Select tblPreference.TU_CODE from tblPreference where tblPreference.TU_CODE = tblTutor.TU_CODE AND tblPreference.[TU_COURSE] = '" & course & "')"

Any help would be really gratefully appreciated!

 

[Guus2005]

For future reference: If you post your query with tables and sample records in a database it is easier to debug.

Put a breakpoint at the next line and run the code. When it stops, press control-g to enter the immediate window and press

?strsql2

the result will be the query you want to execute. Put this string in a query and run it. The error message should tell you where to look.

Remember that "WHERE NOT EXISTS" can often be replaced by a faster LEFT or RIGHT OUTER JOIN.

HTH

 

[nur_arifien]

Pleased help my case:

Hello master I need a help on Microsoft Access database with php view website

I have a database like this
Student
|studentid | name | remark|
|V001 |John | 100 |
|V002 |Saan | 80 |
|V003 |Tom | 40 |

Grade
|gradeid | remark| grade|
|G1 |100 | A |
|G2 |60 | C |
|G3 |40 | D |

my query are:

SELECT student.studentid, student.name, student.remark, 
IIF([student.remark]=(SELECT grade.remark
FROM grade WHERE (((grade.gradeid)="G1"));),"valid","invalid   ") AS status
FROM student;

Result::
Query
|studentid | name | remark| status|
|V001 |John | 100 | valid |
|V002 |Saan | 80 | invalid |
|V003 |Tom | 40 | invalid |

Problem:
When I view my table `query` I got an error on php

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1., SQL state 07001 in SQLExecDirect in C:\xampplite\htdocs\proses2.php on line 6
Error in query

but if i change my query into:

SELECT student.studentid, student.name, student.remark, 
IIF([student.remark]=(SELECT grade.remark
FROM grade WHERE (((grade.remark)="100"));),"valid","invalid   ") AS status
FROM student;

It's work

My mean to is when ever i change the value of grade.remark (dynamic).
can any one analyst my code above.
Thanks for ur help
Regards,