Query pulling data it shouldn't

[Guest]

I have this query:
SELECT DSM.MajorDep, Psychotropic.Prescribed
FROM (Info INNER JOIN DSM ON (Info.TodaysDate = DSM.DSMDate) AND (Info.ResId
= DSM.ResId)) INNER JOIN Psychotropic ON (Info.TodaysDate =
Psychotropic.PsychotropicDate) AND (Info.ResId = Psychotropic.ResId)
GROUP BY DSM.MajorDep, Psychotropic.Prescribed, Info.ResId, Info.TodaysDate
HAVING (((DSM.MajorDep)=1) AND ((Info.ResId)=[whichId]) AND
((Info.TodaysDate)=Date())) OR (((Psychotropic.Prescribed)=1));

Sometimes MajorDep in the table DSM will not have any record in the table.
But when I run this query and the MajorDep is not in the table it is pulling
up another record instead.

So If i enter 10 for the ResId and have no 10 ResId corresponding to the
Date() I would like the query to come up with no records in it. But it is
actually pulling another record with a completely different ResId's MajorDep
so the query has 1 record in it. Does this make sense? Did I miss something?

 

[Guest]

It sounds like you are missing a join somewhere.

On a few of the tables you joined different colums. This may be correct but
to me (I'm just a student of SQL so I could be wrong) it shouldn't be. For
example "Info.TodaysDate = DSM.DSMDate" and "Info.TodaysDate =
Psychotropic.PsychotropicDate". Or maybe a join was missed somwhere else all
together?

Just some ideas.