Thread: Syntax/Pseudocode problem
02-25-08, 14:17 #1Registered User
- Join Date
- Jan 2008
Unanswered: Syntax/Pseudocode problem
I was wondering if anyone has some advise as to how i should go about a problem i have. The following code finds a tutor with free space as long as their preference does not equal a certain course; the problem with this is that my tblPreference is structured in such a way that a tutor may have multiple entries
eg TU_CODE TU_COURSE TU_PREF
77 TR099 Y
77 TR011 N
At the moment the sql statement finds the first entry the tutor has in tblPreference and if its not equal to 'course' thats fine; the problem is that its not checking all subsequent entries the tutor has in tblPreference; so they might infact have stated that they do not want that course but my sql syntax is not picking that up. Does anyone have any suggestions as to how i can go about solving this please?
strsql2 = "SELECT tblTutor.TU_CODE " _
& "FROM (tblTutor left JOIN [tblStudents] ON [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE) " _
& "left join tblPreference on (tblTutor.TU_CODE = tblPreference.TU_CODE) " _
& "GROUP BY tblTutor.TU_CODE, tblTutor.TU_CHAMBER_SIZE, tblTutor.TU_COURSE, tblTutor.TU_ONLY, tblStudents.STU_TU_CODE, tblPreference.TU_CODE, tblPreference.TU_COURSE " _
& "HAVING ([TU_CHAMBER_SIZE] > (Count([tblStudents].STU_TU_CODE)) AND tblPreference.[TU_COURSE] <> '" & course & "')"
02-25-08, 18:49 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
Try Breaking the Query down more. Sometime you want to run a couple of query getting the data 80%-90% right make the 3rd , 4th the stuff you want to see.
ill even written Query first then write the SQL to feel off that query
Just one thing to [b]rem[\b] never have a Query pointing to a foum then get SQL to read that Query MS dose not like that alll .
Last edited by myle; 02-25-08 at 18:53.hope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON