Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Posts
    4

    Question Unanswered: Access Query help pulling miltiple records

    Please help I am trying to do an access query using an inner join...I am using an or to seperate the statements after the inner join...Can this not be done? I need a way to pull the records just once and right now it is duplicating them...Any help would be great!

    Code:
            <cfquery name="formWait" datasource="#datasource#">
             SELECT distinct demographics.colors, demographics.colors2, formApprasialStateP.rec_ID, formApprasialStateP.keyName, 
             formApprasialStateP.form_id, formApprasialStateP.teacherID as sid, formApprasialStateP.teacherStatus, 
             formApprasialStateP.teacherStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.appraiserStatus, formApprasialStateP.cycleYear as 
             ty, formApprasialStateP.cycle as tdc, formApprasialStateP.saved, formApprasialStateP.status 
             FROM formApprasialStateP INNER JOIN demographics ON formApprasialStateP.status = demographics.colors or formapprasialstatep.status=demographics.colors2         where teacherStatus = 2  and appraiserStatus = 1 and teacherID=#id#
            </cfquery>

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT distinct 
           demographics.colors
         , demographics.colors2
         , formApprasialStateP.rec_ID
         , formApprasialStateP.keyName
         , formApprasialStateP.form_id
         , formApprasialStateP.teacherID as sid
         , formApprasialStateP.teacherStatus
         , formApprasialStateP.teacherStatus
         , formApprasialStateP.appraiserStatus
         , formApprasialStateP.appraiserStatus
         , formApprasialStateP.cycleYear as  ty
         , formApprasialStateP.cycle as tdc
         , formApprasialStateP.saved
         , formApprasialStateP.status 
      FROM formApprasialStateP 
    INNER 
      JOIN demographics 
        ON formApprasialStateP.status 
           IN ( demographics.colors, demographics.colors2 )
     where teacherStatus = 2  
       and appraiserStatus = 1 
       and teacherID = #id#
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Posts
    4
    I tried to run that and got the followig error

    Error Executing Database Query.
    [Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'formApprasialStateP.status IN ( demographics.colors, demographics.colors2 ) where teacherStatus = 2'.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    weird

    i tested the JOIN.. ON.. IN(...) and it works fine, except if there's anything after the IN(...), such as a WHERE clause or ORDER BY clause

    stupid $#%@# access!!

    try it this way, i.e. with parentheses --
    Code:
      FROM formApprasialStateP 
    INNER 
      JOIN demographics 
        ON (
           formApprasialStateP.status = demographics.colors
        OR formApprasialStateP.status = demographics.colors2 
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2007
    Posts
    4
    I dont get an error with that one, but it pulls multiples...If I run it in access this is what I get....I know I hate access to! The red ones are duplicates


    hr 413 10 342 805 2 2 1 1 1 1 2 hr
    hr 414 10 343 805 2 2 1 1 1 1 2 hr
    hr 424 10 352 805 2 2 1 1 1 1 2 hr
    hr 425 10 353 805 2 2 1 1 1 1 2 hr
    hr finalStaff 405 10 334 805 2 2 1 1 1 1 2 finalstaff
    hr finalStaff 413 10 342 805 2 2 1 1 1 1 2 hr
    hr finalStaff 414 10 343 805 2 2 1 1 1 1 2 hr
    hr finalStaff 424 10 352 805 2 2 1 1 1 1 2 hr
    hr finalStaff 425 10 353 805 2 2 1
    1 1 1 2 hr

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, the red ones are not duplicates

    hr finalStaff 414 10 343 805 2 2 1 1 1 1 2 hr
    hr finalStaff 424 10 352 805 2 2 1 1 1 1 2 hr
    hr finalStaff 425 10 353 805 2 2 1 1 1 1 2 hr
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Posts
    4
    Yes They are....The ones highlighted in colors are the rec_id and that is an autonumber field so yes they are duplicates.

    hr 413 10 342 805 2 2 1 1 1 1 2 hr
    hr 414 10 343 805 2 2 1 1 1 1 2 hr
    hr 424 10 352 805 2 2 1 1 1 1 2 hr
    hr 425 10 353 805 2 2 1 1 1 1 2 hr
    hr finalStaff 405 10 334 805 2 2 1 1 1 1 2
    hr finalStaff 413 10 342 805 2 2 1 1 1 1 2 hr
    hr finalStaff 414 10 343 805 2 2 1 1 1 1 2 hr
    hr finalStaff 424 10 352 805 2 2 1 1 1 1 2 hr
    hr finalStaff 425 10 353 805 2 2 1 1 1 1 2 hr
    Last edited by nrutter; 01-25-07 at 16:34.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    unfortunately that is not how DISTINCT works

    DISTINCT applies across all columns selected

    if you want one row per rec_id, you will probably want to use GROUP_BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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