Friday, February 24, 2012

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!

<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>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#|||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'.|||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 -- FROM formApprasialStateP
INNER
JOIN demographics
ON (
formApprasialStateP.status = demographics.colors
OR formApprasialStateP.status = demographics.colors2
)|||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|||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|||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|||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

No comments:

Post a Comment