Subquery with multiple criteria

beckyr

Registered User.
Local time
Today, 17:45
Joined
Jan 29, 2008
Messages
35
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!
 
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
Code:
?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:D
 
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:
Code:
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:
Code:
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,
 

Users who are viewing this thread

Back
Top Bottom