Excluding Values

This tip is suitable for all versions of Microsoft Access

Excluding Values in a Query (Example One)

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 (Example Two)

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

Try the following:

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.

Return from Excluding Values to Simply Access Home Page

Return from Excluding Values to Microsoft Access Queries