Why is my query acting like this

[Guest]

SELECT DSM.MajorDep, Psychotropic.Prescribed, Safety.SafetyRisk, Info.ResId,
Info.TodaysDate
FROM ((Info INNER JOIN DSM ON (Info.ResId = DSM.ResId) AND (Info.TodaysDate
= DSM.DSMDate)) INNER JOIN Psychotropic ON (Info.ResId = Psychotropic.ResId)
AND (Info.TodaysDate = Psychotropic.PsychotropicDate)) INNER JOIN Safety ON
(Info.ResId = Safety.ResId) AND (Info.TodaysDate = Safety.SafetyDate)
WHERE (((Safety.SafetyRisk)=1) AND ((Info.ResId)=[whichId]) AND
((Info.TodaysDate)=Date())) OR (((Psychotropic.Prescribed)=1)) OR
(((DSM.MajorDep)=1));


I have in my tables like this
ResId =4, SafetyRisk = 1, Prescribed = 0, and MajDep = 0
ResId =2, SafetyRisk = 1, Prescribed = 0, and MajDep = 1

and I put in 4 the ResId
the query is pulling
ResId =4, SafetyRisk = 1, Prescribed = 0, and MajDep = 1

So basicly it is taking MajDep value from ResId = 2 and pulling it into this
query.
It should be 0 from the ResId = 4.

Does anyone have any ideas?

 

[Vincent Johns]

Did you declare [ResId] to be the primary key in each of the Tables in
which it appears? If not, you may have duplicate values of that field,
such as 2 records in [DSM] in which [ResId] = 4. For example, perhaps
[DSM] contains records like these:

[DSM] Table Datasheet View:

ResId MajorDep DSMDate
----- -------- ---------
2 1 1/25/2006
4 0 1/25/2006
4 1 1/25/2006

But I wasn't able to get your Query to return only the one record that
you described. For example, if your parameter [whichID] = 4, then the
Query returns these records:

ResId SafetyRisk Prescribed MajorDep TodaysDate
----- ---------- ---------- -------- ----------
4 1 0 0 1/25/2006
4 1 0 1 1/25/2006
2 1 0 1 1/25/2006

If you wish it to return some other set, then you may have to define
your Query differently. What kind of results did you want to see?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.