Quering Not Nulls 1

[Hillary]

I am working with two different databases that each have health information for the same set of students (each database has one record for each student). I am attempting to "clean up" the data and make sure the health alerts in both databases match. I am trying to create a query which lists all students who have a health alert in either system.

I have a table from each database linked in a query. The tables are linked by student ID and the query shows
Student ID
First Name
Last Name
Alert
Medical Alert
([Alert] and [Medical Alert] are both Medical Alert fields from different databases.)

I have 3 rows of criteria in my query...
[Alert] Is Not Null
or
[Medical Alert] Is Not Null
or
[Alert] Is Not Null and [Medical Alert] Is Not Null

I was expecting only records with a medical alert in one or both systems to return but instead all records returned.

Next, I tested for nulls by creating a new query and placing the formula Null Alert: IsNull([Alert]) and Null Medical Alert: IsNull([MEDICAL_ALERT]).

The system returned -1 where there is not a medical alert and a 0 where there is a medical alert. Lastly, I added the following criteria...
[Null Alert] 0
or
[Null Medical Alert] 0
or
[Null Alert] 0 and [Null Medical Alert] 0

But again, all records are returning.

How do I query all students who have a medical alert is one database or the other?

Thanks,

Hillary

 

[PHV]

all students who have a health alert in either system.
WHERE Not ([Alert] Is Null And [Medical Alert] Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886