Solved

Query excluding

Posted on 2010-08-29
28
468 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

864 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

19 Experts available now in Live!

Get 1:1 Help Now