Excluding Records when field has multiple values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I currently have a query that contains a field which can have multiple number
values (up to 25). Currently the query pulls in the data and creates
duplicate rows where all other fields are identical except the multi-value
field.

I need the query to exclude the "entire" record if the field equals 10
certain number values. Right now I can only get the query to exclude those
duplicate rows that meet the criteria. Can someone guide me on how to do
this?

Thanks in advance!
 
One way is to create a reference table of the values to exclude. If it
changes a lot then add a field named Active as a Yes/No field.
In your query add the reference table and do not join.
For value criteria use <>[Value] on your multiplier field.
If you include the Active field us -1 (that is minus one) as criteria.
 
Thanks Karl, boy did I try that the hard way the first time around! Your tip
was great.

However, this still just excludes the duplicated row with that value in it.
Here is sample data as my query is now:

AcctNo MultiField
301 10
301 14
301 16
302 12
302 14
303 12
303 20
303 24
304 14
304 18

12 is my exclusion value (code). From this sample data, I do not want Acct#
302 or 303 to return on any row.

If I bounce it up against our exclusion reference table with <>[value] in
the criteria I get:
301 10
301 14
302 14
303 20
303 24
304 14
304 18

I need Acct 302 and 303 not to return at all... like this:

301 10
301 14
304 14
304 18

Basically, if the account has any of those exclusion values on my reference
table, I don't want any data to return for that account. Can you help me
with this problem?




KARL DEWEY said:
One way is to create a reference table of the values to exclude. If it
changes a lot then add a field named Active as a Yes/No field.
In your query add the reference table and do not join.
For value criteria use <>[Value] on your multiplier field.
If you include the Active field us -1 (that is minus one) as criteria.

GinaJ said:
I currently have a query that contains a field which can have multiple number
values (up to 25). Currently the query pulls in the data and creates
duplicate rows where all other fields are identical except the multi-value
field.

I need the query to exclude the "entire" record if the field equals 10
certain number values. Right now I can only get the query to exclude those
duplicate rows that meet the criteria. Can someone guide me on how to do
this?

Thanks in advance!
Click to expand...
Click to expand...
 
Back
Top