Remove records if it contains with any of the following value

[RoyS]

i am trying to exclude records when the field contains with any of the text.


For example, John has the following codes: (SOY, NOC, NME, NMA); Mary as the following codes: (NMA and NOC); and Joshua has the following codes: (NMA)

I want to exclude records that contain either SOY or NOC. The correct result should only show Joshua. When i used Not in on the query, it does not give the correct result.

[alansidman]

Show us your current SQL statement

[RoyS]

SELECT testing.[first name], testing.[last name], testing.yea, Sum(testing.amt) AS SumOfamt, testing.pdim, [address 2].address, [address 2].city, [address 2].state, [address 2].zip, exclusion.exclusion
FROM (testing INNER JOIN [address 2] ON testing.pdim = [address 2].pidm)
INNER JOIN exclusion ON [address 2].pidm = exclusion.pidm
GROUP BY testing.[first name], testing.[last name], testing.yea, testing.pdim, [address 2].address, [address 2].city, [address 2].state, [address 2].zip, exclusion.exclusion
HAVING (((testing.yea)>="2013") AND ((Sum(testing.amt))>=50) AND ((testing.pdim) Like "z*") AND ((exclusion.exclusion) Not In ("NOC","SOY")));

[CJ_London]

it does not give the correct result.

does not help us help you - for the future explain the you are getting as well as the result you expect. So we have to speculate what the problem is and offer a number of possible solutions

three of your criteria should be in the where part of the code

...
...
WHERE testing.yea>="2013" AND testing.pdim Like "z*" AND exclusion.exclusion Not In ("NOC","SOY")
GROUP BY testing.[first name], testing.[last name], testing.yea, testing.pdim, [address 2].address, [address 2].city, [address 2].state, [address 2].zip, exclusion.exclusion
HAVING Sum(testing.amt)>=50

I presume that John has 4 records (one each for SOY, NOC, NME, NMA), Mary 2 and Joshua 1

Also having a field name the same as the table name will cause issues at some point - probably this one since the syntax otherwise looks OK.

if John has one record with an exclusion field value of "SOY, NOC, NME, NMA", then your query won't work. Instead, since your data is not normalised you need

...AND (exclusion.exclusion Not like "*NOC*" OR exclusion.exclusion Not Like "*SOY*")

Finally, if your exclusion field is a multivalue field, then you need to refer to the .value property of the field

.... AND exclusion.exclusion.value Not In ("NOC","SOY")

[RoyS]

thank you for the feedback. i put this sample data quickly and didn't follow the best practice.
Your assumption is correct: John has 4 records (one each for SOY, NOC, NME, NMA), Mary 2 (NOC, NME) and Joshua 1 (NMA)
i would like to know on Access, what to put in the criteria to remove record contains any of these value (SOY, NOC). Attached is the print screen.

[ssanfu]

One other point: it doesn't make any sense having a criteria of

testing.yea >= "2013"

"2013" is a TEXT string. The "Greater than or equal to" condition won't work/doesn't make sense with a text string.

If the data stored in the field "testing.yea" are years, I would suggest the data type should be Integer or Long Integer instead of Text.

[CJ_London]

would like to know on Access, what to put in the criteria to remove record contains any of these value (SOY, NOC).

look at my first suggestion. If you want to see or edit the sql your query generates, click on the View option at the top left on the ribbon and select SQL, or click on SQL to the bottom right of your screen.