Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008
    Posts
    37

    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 & "')"

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,469
    Provided Answers: 10
    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

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •