Exclude Records where last char is alpha or is the same without alpha

[sxschech]

[SOLVED] Exclude Records where last char is alpha or is the same without alpha

Trying to figure out how to exclude those records that have an alpha as the last character and also exclude the records that have the same id code without the alpha.

Example Listing:

id_code
ak_12345
ak_12345a
ak_12346
gl_2391
1009123
1009128
1009128a
1009128b

Desired Output Result:

id_code
ak_12346
gl_2391
1009123

The length of characters in the id_code is not consistent. Some id_codes are longer and some are all numeric and some don't have underscores.

 

[vbaInet]

You can use StrReverse() or Right() and IsNumeric() functions.

 

[sxschech]

I understand about using string functions, but for some reason, drawing a blank on how to exclude both the "base" id as well as the id with the alpha at the end. I imagine I would need to join the table to another query, but still not getting it.

 

[jdraw]

You could count the final base strings, you only want the ones that have count =1, I think.

 

[vbaInet]

Ok since you already know how to extract the relevant id_codes you need to then do a NOT LIKE id_code & "*" join.

 

[CJ_London]

rather than trying to work on the basis of excluding, have you tried working on the basis of including - something like

SELECT myTable.id_code
FROM myTable LEFT JOIN (SELECT First(IIf(IsNumeric(Right([id_code],1)),[id_code],Left([id_code],Len([id_code])-1))) AS [idcode]
FROM myTable
GROUP BY IIf(IsNumeric(Right([id_code],1)),[id_code],Left([id_code],Len([id_code])-1))
HAVING (((Count(myTable.[id_code]))>1)))  AS D ON myTable.id_code = D.idcode
WHERE (((D.idcode) Is Null) AND ((IsNumeric(Right([id_code],1)))=True))

 

[vbaInet]

I was thinking along the lines of:

SELECT [COLOR="blue"]TableName[/COLOR].id_code
FROM [COLOR="blue"]TableName[/COLOR]
WHERE (SELECT Count(*) 
       FROM [COLOR="Blue"]TableName [/COLOR]AS A 
       WHERE A.id_code LIKE [COLOR="blue"]TableName[/COLOR].id_code & "[a/b]"
      )=0 
      AND 
      IsNumeric(Right([id_code],1))=True;

 

[CJ_London]

@vbaInet - neat solution

Not seen this before as a construct

... LIKE TableName.id_code & "[a/b]"

I can see it works but what is it actually doing? I thought it might be for special characters?

 

[vbaInet]

[a/b] is equivalent to 'a' or 'b'.
So it will match the ones that are numeric but end with either an 'a' or 'b', and of course the second clause will negate those.

 

[CJ_London]

Ah, so if there is a 'c' as well it needs to be "[a/b/c]"

Like it, learn something new every day

 

[Brianwarnock]

Haven't got access so can't try but can you use [a-z] i'm sure my old brain remembers something like that.

Brian

 

[vbaInet]

Absolument CJ!

It's a construct used in regular expressions and I'm surprised that it's not mentioned in the help files.

 

[vbaInet]

Haven't got access so can't try but can you use [a-z] i'm sure my old brain remembers something like that.

Brian

Are you sure your computer will boot up after those many years? Yes a range will work too.

 

[Brianwarnock]

Are you sure your computer will boot up after those many years? Yes a range will work too.

Nope it wont, using a new laptop with no Access, hell I have enough problems with Excel 2010 and win 8.1.

Oh! the brain has difficulty booting too.

Brian

 

[vbaInet]

I hope you're transferring all that knowledge to the grandson.

 

[stopher]

Ah, so if there is a 'c' as well it needs to be "[a/b/c]"

Like it, learn something new every day

The correct syntax would be [abc]. vbaInet's solution should be [ab]. The "/" would mean searching for "/" as well as "a" or "b".

See here.

 

[vbaInet]

Quite right Chris. Perhaps I was thinking of Perl :S

 

[vbaInet]

I was thinking of Perl's Or construct but it isn't even "/", it's "|"

 

[sxschech]

Great discussion. I think I got it working, it took 5 minutes to run. When I incorporated it into the actual query, Access 2007 kept crashing on me. I think maybe because too many records and not enough memory. I'm hoping to buy some additional ram soon. I ended up doing a make table to cut the size down and at least on the small sample data set, looks like is working. I'll hand over what I've got to the person that will be using it (he has plenty of ram) and see how it goes in the production environment.

Thanks for the [a-z] tip. With that, I searched on access query [az] and found this page which says applies to Access 2003, I assume also applies to newer versions.

http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx

Since I tested on small sample and it seems to work, I'll see how it goes next week in production and assuming all is well, I'll do the appropriate thanks at that point.

 

[vbaInet]

How many records are you running this against? And which query did you use in the end?