Criteria for Query Question

[TotallyConfused]

How do I write criteria for the following? I have to identify ids
(combination of text and numbers) that have 8 characters. However the 8th
character can change if the id is dependent on main id. For instance,
FRC5TTZA, FRC5TTZB, same 7 characters but different last character. There
are instances where the 7 character will change for instance ABCDEF10 and
ABCDEF20. ABCDEF20 is dependent on ABCDEF10. Can someone please help me how
to pull all main ids and their dependents? Thank you in advance of any help
you can provide.

 

[John W. Vinson]

How do I write criteria for the following? I have to identify ids
(combination of text and numbers) that have 8 characters. However the 8th
character can change if the id is dependent on main id. For instance,
FRC5TTZA, FRC5TTZB, same 7 characters but different last character. There
are instances where the 7 character will change for instance ABCDEF10 and
ABCDEF20. ABCDEF20 is dependent on ABCDEF10. Can someone please help me how
to pull all main ids and their dependents? Thank you in advance of any help
you can provide.

It sounds like you're trying to make one field serve two purposes - sometimes
the last character (or sometimes the last TWO characters) have a different
meaning and different function than the rest of the field.

Fields should be "atomic" - in a properly normalized database, you should
never need to pick apart a field into subcomponents in order to use it! Is
there any way you can separate this into two fields? If you do so, you can
always concatenate the two fields into one for display purposes.

If you can't do this, please explain the logic. Why are ABCDEF10 and ABCDEF10
the "same"? Because the last two characters are numeric, or some other logic?

 

[TotallyConfused]

Thanks for responsonding. ABCDEF10 is a type. Should have read ABCDEF20

The data I get is imported as such the one field "ID" is a combination of
text and numbers and last two characters are either text or numbers. Just
trying to identify all ids that have a B, C, D, etc and 20, 30 , 40 etc.

Can this be done? even if I have to run two separate queries?

 

[John W. Vinson]

Thanks for responsonding. ABCDEF10 is a type. Should have read ABCDEF20

The data I get is imported as such the one field "ID" is a combination of
text and numbers and last two characters are either text or numbers. Just
trying to identify all ids that have a B, C, D, etc and 20, 30 , 40 etc.

Can this be done? even if I have to run two separate queries?

Yes, it can be done... but I still don't understand the logic!

You can find all records where the last two digits are numeric with a query
criterion

LIKE "??????##"

Or all records where the last character is alphabetic

LIKE "???????[A-Z]"

Or all records where the 7th character is alphabetic but the 8th is numeric

LIKE "??????[A-Z]#"