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,424
    Provided Answers: 8
    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 19:53.
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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