SELECT ResponseField.ResponseGroupID, ResponseField.ResponseFieldName,
PersonResourceResponse.PersonResourceResponseID,
PersonResourceResponse.ScenarioID, PersonResourceResponse.UserInput,
PersonResourceResponse.ResourceResponse, PersonResourceResponse.IsIrritated,
PersonResourceResponse.ResponseFieldID, PersonResourceResponse.PersonID
FROM PersonResourceResponse RIGHT JOIN ResponseField ON
PersonResourceResponse.ResponseFieldID = ResponseField.ResponseFieldID;
This works great...gives me all records in ResponseField and then the
matching values, if any, in PersonResourceResponse.
However, if I include the criterea WHERE PersonResourceResponse.PersonID = 3
or PersonResourceResponse Is NULL, I only get the (existing) records in
PersonResourceResponse where the PersonID is 3 or null. What I was
expecting is to get existing records, plus the records from ResponseField
that have no matches in the PersonResourceResponse.
I'm not exactly and SQL newbie, but I can't get my head around what's going
wrong here.
TIA;
Amy
Thanks;
Amy
"Amy Blankenship" <Amy_n...@magnoliamultimedia.com> wrote in message
news:%23r8saO$2FHA...@TK2MSFTNGP15.phx.gbl...
Create one query to do the WHERE, and a second to perform the RIGHT JOIN.
Base the latter on the former.
--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
"Amy Blankenship" <Amy_n...@magnoliamultimedia.com> wrote in message
news:%23r8saO$2FHA...@TK2MSFTNGP15.phx.gbl...
I'm wondering if a NOT In would work...
Thanks;
Amy
"[MVP] S.Clark" <steve.cla...@FMSInc.com> wrote in message
news:%23lM9jn$2FHA...@TK2MSFTNGP09.phx.gbl...
Amy,
If you manage to get the frustrated outer join to be updateable, post
back here and let me know.
Sincerely,
Chris O.
Query 1:
SELECT PersonResourceResponse.PersonResourceResponseID,
PersonResourceResponse.ScenarioID, PersonResourceResponse.PersonID,
PersonResourceResponse.UserInput, PersonResourceResponse.ResourceResponse,
PersonResourceResponse.IsIrritated, PersonResourceResponse.ResponseFieldID
FROM PersonResourceResponse
WHERE (((PersonResourceResponse.ScenarioID)=GetScenario()) AND
((PersonResourceResponse.PersonID)=GetPerson() Or
(PersonResourceResponse.PersonID) Is Null)) OR
(((PersonResourceResponse.ScenarioID) Is Null));
In other words, this filters the records down to what the where clause would
filter them to, but before the other table and the join comes into the
picture. I wound up needing two inner criteria, because when I moved from
one scenario to the next, the scenario filter failed, of course.
The functions simply returned the value of Public variables: theScenario and
thePerson.
Then:
SELECT ResponseField.ResponseGroupID, ResponseField.ResponseFieldName,
noAWPersonResponseFilter.PersonResourceResponseID,
noAWPersonResponseFilter.ScenarioID, noAWPersonResponseFilter.PersonID,
noAWPersonResponseFilter.UserInput,
noAWPersonResponseFilter.ResourceResponse,
noAWPersonResponseFilter.IsIrritated,
noAWPersonResponseFilter.ResponseFieldID
FROM ResponseField LEFT JOIN noAWPersonResponseFilter ON
ResponseField.ResponseFieldID = noAWPersonResponseFilter.ResponseFieldID;
This is an updateable query.
The scenario form OnCurrent sets the theScenario to Me.ScenarioID, unless
ScenarioID is NULL, in which case it sets theScenario on ScenarioID change.
The noAW in the query title tells me I can't run this query from outside
Access, because of the custom functions.
The form that has the above query as its rowsource is embedded in a form
that is based on PersonID. Unfortunately you can't depend on the
LinkMaster/LinkChild relationship to work, because you don't just want the
ones where PersonID is =, you want = or null. So I actually used
PersonResponseGroupID, which limits the return to only the types of
responses that person can give, for the Master/Child. In the OnCurrent for
the outer form, thePerson is set to Me.PersonID. In this case, no new
people would be added, so there was no need to trap for PersonID being null.
However, because the subform is embedded and apparently already loaded, I
did have to requery.
I also disabled all buttons until there was a valid ScenarioID on the page,
because launching the popup that depended on theScenario when theScenario
was wrong was pretty icky!
So you just got the benefit of about 8 hours of extreme frustration,
hair-pulling, teeth gnashing, and husband snapping-at.
Hope it helps :-);
Amy
"Chris2" <rainofstee...@GETRIDOF.luminousrain.com> wrote in message
news:yaGdnRta-uL...@comcast.com...
... RIGHT JOIN ResponseField ON (PersonResourceResponse.ResponseFieldID =
ResponseField.ResponseFieldID and PersonResourceResponse.PersonID = 3) ...
This is one of the difference between INNER JOIN and OUTER JOIN: with the
OUTER JOIN, the order of application of the criterias is relevant.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Amy Blankenship" <Amy_n...@magnoliamultimedia.com> wrote in message
news:%23r8saO$2FHA...@TK2MSFTNGP15.phx.gbl...