Does Not Include

[Neil_Pattison]

I have a table in MS Access (I've attached a few records for your info in excel format) where I need to exclude certain records.

Each record has a number attached to it in the first column (the example shows 1 to 14). In the final column it shows either "Initial Visit" or "Maintenance".

The query I require needs to exclude any records that show "Maintenance" and also any records that have the same number in the first column.

So in the example I've attached, the records with the numbers 1; 5 & 8 need to be removed, leaving the remaining records.

Can anyone please help me with this?

Many thanks

 

[vbaInet]

Here are the steps:

1. Create an alias field to count reoccurences using the DCount() function. Something like:

CountDoubleIDs: DCount("*", "TableName", "[subCutNumber] = " & [subCutNumber])

http://www.techonthenet.com/access/functions/domain/dcount.php
2. Under the criteria row of the field from step 1, put < 1
3. Under the criteria row of subCutType, put <> "Maintenance"