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 free weekly Newsletter to receive tips weekly via email. Click Here to subscribe.