Solved

Query excluding

Posted on 2010-08-29
28
455 Views
Last Modified: 2012-05-10
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
Comment
Question by:wwstudioinc
  • 10
  • 4
  • 4
  • +4
28 Comments
 
LVL 13

Expert Comment

by:nike_golf
ID: 33555001
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
 
LVL 13

Expert Comment

by:nike_golf
ID: 33555034
If its Access you can use something like should work.

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

or

Like "?[yY]*"

0
 

Author Comment

by:wwstudioinc
ID: 33555054
Sorry my mistake it should be excluding Y
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555057
Exclude where ends with Y, and the column is at least 2 characters long

Select * from Tbl where NOT Col Like "*?y"
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555082
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33555090
try

Select * from Tbl where col NOT Like "*Y"
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555116
> 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
 
LVL 30

Expert Comment

by:hnasr
ID: 33555161
If your data is:
Y
DY
FY
Hy
EY
AY

What output do you expect?
0
 

Author Comment

by:wwstudioinc
ID: 33555174
hnasr
y
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33555188
Just :
Y


Or:
Y
Y
Y
Y
Y
Y

?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33555216
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555231
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
 
LVL 13

Expert Comment

by:nike_golf
ID: 33555247
Not Like "*[y]*"

NG,
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 33555301
looks like access is case insensitive so Y & y are treated same way ;)
so most of the solutions are not valid ;) think again...
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 30

Expert Comment

by:hnasr
ID: 33555302
Try this: table types (type)

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

Expert Comment

by:cyberkiwi
ID: 33555311
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33555345
<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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555395
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
 

Author Comment

by:wwstudioinc
ID: 33555557
the expected output should be "Y"  a total of one record
If your data is:
Y
DY
FY
Hy
EY
AY
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555804
Select * from Tbl where Col <> 'Y'
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33555806
Sorry, I meant

Select * from Tbl where Col = 'Y'

Based on your answers so far.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 33557359
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
 

Expert Comment

by:alivar
ID: 33562619
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33583312
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
 
LVL 51

Expert Comment

by:HainKurt
ID: 33583321
oops, a fix for above, use this:

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

Author Comment

by:wwstudioinc
ID: 33613814
I have attached a sample to see the problem i am encountering with the db
db1.zip
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33614014
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
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33614029
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
This collection of functions covers all the normal rounding methods of just about any numeric value.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now