PLEASE help with access query

[joe_G]

I was wondering if someone could help me with an access query. I'm
faily new to running this type of query so any advice would be
helpful.

Basically, I have a table with three fields, State, Product and
Product Code:

A sampe would like this

state product product code

NJ, Wine, AA,WW,SR,RW

Here is the problem, I have over 4500 records in this database and
product code WW is a problem. I need to filter the report so no
instance of product code WW appears anywhere. In other words, if
product code WW appears in the record, it needs to be filtered. I have
tried all kinds of NOT LIKE statements, but it is not catching all the
records: Product Code WW can appear anywhere in the product code
string

HERE IS A SAMPLE OF THE QUERY I AM USING, BUT IT IS NOT CATCHING ALL
THE RECORDS WITH PRODUCT CODE WW IN THE STRING:

Not Like "*, WW,*" And (Not Like "WW,*") And (Not Like "WW*")

 

[John W. Vinson]

I was wondering if someone could help me with an access query. I'm
faily new to running this type of query so any advice would be
helpful.

Basically, I have a table with three fields, State, Product and
Product Code:

A sampe would like this

state product product code

NJ, Wine, AA,WW,SR,RW

Here is the problem, I have over 4500 records in this database and
product code WW is a problem. I need to filter the report so no
instance of product code WW appears anywhere. In other words, if
product code WW appears in the record, it needs to be filtered. I have
tried all kinds of NOT LIKE statements, but it is not catching all the
records: Product Code WW can appear anywhere in the product code
string

HERE IS A SAMPLE OF THE QUERY I AM USING, BUT IT IS NOT CATCHING ALL
THE RECORDS WITH PRODUCT CODE WW IN THE STRING:

Not Like "*, WW,*" And (Not Like "WW,*") And (Not Like "WW*")

The basic problem is of course that your product code is not atomic - you seem
to have a one to many relationship embedded in one field!

Given the current design, though, you need to remove three cases: WW at the
beginning, WW in the middle, and WW at the end. If the data is consistant,
with two letter codes separated by a comma with no blanks, then

NOT LIKE "WW*" AND NOT LIKE "*,WW*"

should catch all of them; the second criterion will exclude both medial and
terminal instances.

 

[joe_G]

The basic problem is of course that your product code is not atomic - youseem
to have a one to many relationship embedded in one field!

Given the current design, though, you need to remove three cases: WW at the
beginning, WW in the middle, and WW at the end. If the data is consistant,
with two letter codes separated by a comma with no blanks, then

NOT LIKE "WW*" AND NOT LIKE "*,WW*"

should catch all of them; the second criterion will exclude both medial and
terminal instances.

Thank you so much, I will try it out

 

[John Spencer]

Actually IF all the product codes are two letter codes all the user
would need would be

NOT LIKE ("*WW*")

Of course, if you could have codes like AWWQ or WWF then all the
solutions proposed would fail depending on whether or not you wanted to
include or exclude AWWQ and/or WWF.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Actually IF all the product codes are two letter codes all the user
would need would be

NOT LIKE ("*WW*")

Of course, if you could have codes like AWWQ or WWF then all the
solutions proposed would fail depending on whether or not you wanted to
include or exclude AWWQ and/or WWF.

I understand that the World Wildlife Fund and the World Wrestling Federation
have agreed to settle their longstanding dispute about the use of the WWF
acronym by merging into the Wildlife Wrestling Foundation...

D'oh! You're quite right of course.