• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

Query excluding

I have a table that has a column name type i would like to exclude any anything that has more that two letter than end in y.the data in the column is looks like this
Y
DY
FY
Hy
EY
AY
i would like to filter it in access and excel.this would give me greater flexibility
0
wwstudioinc
Asked:
wwstudioinc
  • 10
  • 4
  • 4
  • +4
1 Solution
 
nike_golfCommented:
Can you explain a little better what you are trying to do? Are you working in Access or Excel? VBA or Excel functions?

NG,

0
 
nike_golfCommented:
If its Access you can use something like should work.

Like "?y*" or Like "?Y*"

or

Like "?[yY]*"

0
 
wwstudioincAuthor Commented:
Sorry my mistake it should be excluding Y
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
cyberkiwiCommented:
Exclude where ends with Y, and the column is at least 2 characters long

Select * from Tbl where NOT Col Like "*?y"
0
 
cyberkiwiCommented:
In Excel, let's say your data is in A and starts from A2 downwards. You can insert a new column in B, and use this formula for B2 (copy down)

=if(Len(A2)<2,A2,if(Right(A2,1)<>"Y",A2,"")

column B will either be the value of the value in A, or blank if it has 2 characters and ends with upper-case Y.

You can now filter on column B to exclude the data you don't want.
0
 
HainKurtSr. System AnalystCommented:
try

Select * from Tbl where col NOT Like "*Y"
0
 
cyberkiwiCommented:
> has more that two letter than end in y
If the letter before Y must be a "letter", i.e. A-Z, then

Select * from Tbl where Col NOT Like "*[A-Z]y"
0
 
hnasrCommented:
If your data is:
Y
DY
FY
Hy
EY
AY

What output do you expect?
0
 
wwstudioincAuthor Commented:
hnasr
y
0
 
Jeffrey CoachmanMIS LiasonCommented:
Just :
Y


Or:
Y
Y
Y
Y
Y
Y

?
0
 
Jeffrey CoachmanMIS LiasonCommented:
wwstudioinc,

Did you test any of the solutions above?

They all give an output similar to what you are requesting.

It may just be a matter of pickig the exact output you want.

;-)

Jeff
0
 
cyberkiwiCommented:
I beg to differ..
Apart from my 2 solutions (both of which work for the data provided by hnasr and asker), the other solutions either (1) show everything, or (2) exclude everything.
0
 
nike_golfCommented:
Not Like "*[y]*"

NG,
0
 
HainKurtSr. System AnalystCommented:
looks like access is case insensitive so Y & y are treated same way ;)
so most of the solutions are not valid ;) think again...
0
 
hnasrCommented:
Try this: table types (type)

SELECT types.type
FROM types
WHERE (((Len([type]))<3) AND ((Asc(Right([type],1)))<>Asc("Y")));
0
 
cyberkiwiCommented:
wwstudioinc,

In case you get sidetracked, there were two solutions from above that you have not tested.

Exclude where ends with Y (case insensitive), and the column is at least 2 characters long

     Select * from Tbl where NOT Col Like "*?y"

Exclude where ends with Y (case insensitive), and the column is at least 2 characters long, and Y is preceeded by a letter (i.e. A-Z case insensitive)

     Select * from Tbl where Col NOT Like "*[A-Z]y"
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I beg to differ...>

Sorry, ...I only meant to ask the OP to respond, so that all the posts could be evaluated in order...

The OP only posted once, and it was not clear if they evaluated any of the suggestions...


;-)

Jeff
0
 
cyberkiwiCommented:
Jeff,

No worries and very good suggestion!  Having read the question for the 5th time, I no longer think I understand it right.

wwstudioinc,

In case we have misunderstood you, some of the above solutions may work for certain data, but may not have fully appreciated your exact requirements.

Given this dataset, can you please indicate if
(1) the data is not realistic, will not exist, and can be ignored; and
(2) whether it should be INCLUDED in the final result?
Please also note the mix of upper-lower cases and letters, numbers and punctuations.

John Doe
Y
YY
Xy
xY
y
yy
Johnny
JOHNNY
ABC123Y
SPACE Y
Test.y
<empty string>
<null>
0
 
wwstudioincAuthor Commented:
the expected output should be "Y"  a total of one record
If your data is:
Y
DY
FY
Hy
EY
AY
0
 
cyberkiwiCommented:
Select * from Tbl where Col <> 'Y'
0
 
cyberkiwiCommented:
Sorry, I meant

Select * from Tbl where Col = 'Y'

Based on your answers so far.
0
 
hnasrCommented:
I expect the comments here are  to help you in getting you solution, try to ammend any. Do not expect the exact answer with very little contribution from your side, in analyzing the problem.
0
 
alivarCommented:
Your original question says you want to exclude anything that has more that (than) two letter than (that) end in y. Based on your posts after that it sounds like you mean you want to exclude anything that has 2 or more letters and ends in y.

If you are in Design View of your Query. Try entering this in the Criteria row under your TYPE field of your TYPES table:
Not Like "*?Y"

That should give you what you want
0
 
HainKurtSr. System AnalystCommented:
ok, this is the cure for your problem ;)

Select * from Tbl where Right(col,1) <> Asc('Y')

I want all the points ;)
select asc('Y') as Y, asc('y') as y2, right('HainKurt',1) as lastChar

Y	y2	lastChar
89	121	t

Open in new window

0
 
HainKurtSr. System AnalystCommented:
oops, a fix for above, use this:

Select * from Tbl where Asc(Right(col,1)) <> Asc('Y')
0
 
wwstudioincAuthor Commented:
I have attached a sample to see the problem i am encountering with the db
db1.zip
0
 
cyberkiwiCommented:
Just use this

Criteria for Line:     Not Like "*?Y"

It means, do NOT show rows that have 2 or more characters, and ends with Y
0
 
cyberkiwiCommented:
To exclude where it has MORE than 2 characters, AND ends with Y, use this

Criteria for Line:     Not Like "*??Y"

This will remove only the "BBY" s because they are 3 char and end with Y
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 4
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now