Excluding Values

Excluding Values in a Query (1)

I have a query problem. I am trying to exclude data and pull up the rest. Example: I want to exclude all people who have had gum disease, knee trouble and high blood pressure and get all the remainder, being the rest of the conditions for everyone. Is this possible?

To do this:

In the criteria row of the relevant field column, enter:

Not Like "High Blood Pressure" And Not Like "gum disease"......etc.

Excluding values in a Query (2)

Question

My database table has 17 fields.

field 1-5 always have data.

field 6-17 may have a numeric value or may be blank.

first column can have duplicate values.

My question is how can I create a report to only list unique records that will contain data or spaces in field 6-17.

Eg

85535454,ROBERT,JEWELL,,,1,5,5,,2,5,1,,,,

85398204,SHAWN,FALWELL,CAROL,FALWELL,,,,,,,,,,,

85398204,SHAWN,FALWELL,CAROL,FALWELL,1,5,5,,2,5,1,2,5,2,

85738791,REYNA,MITCHELL,,,2,5,3,,1,,2,,,,

E.g. I want a list of only records for:-

 85535454

 85398204 - row that contains values not blanks

 85738791

Answer

To do what you ask.

Open the query in design view.

In the criteria row: for the field values 6-17 type in the following:

Is Not Null

If the criteria  are all in the same row they are all treated as being joined by the 'AND' operand, therefore if any of the fields contain data, this record will be included, as it only needs one record to Not be null, for that record to return a false, thus be included.

To ensure you only receive unique records:

Right click in the top part of the query, in a blank section, i.e. not on any of the tables.

Then select properties from the drop down list:

Next to Unique Values select YES

This will now only display the unique values, and remove any duplicates.

Do you like this tip? Subscribe to my Newsletter to receive tips via email. Click Here to subscribe.