criterea to only return core file numbers?

[Lost500]

Hi everybody,

I'm trying to write a query to only return the 'core' or 'original' records for instance: i have a field called FileNumber and it contains values like

FileNumber:
TX-100
TX-100-A
TX-100-B
TX-500
TX-500-A

the query would only return TX-100 and TX-500 so that i only return the 'core' records or another way to look at it might be only one record for each 'set'. sorry for not knowing the term there but any help would be great!

Thanks so much!
Lost

 

[SkipVought]

Hi,

Where Len(Trim(FileNumber))=6

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[alvechurchdata]

If the FileNumbers vary in length then something like:

group by val(mid(FileNumber,4))

might be a better solution than using a WHERE clause. Even this though relies on the numeric part always starting at position 4 and it's going to be difficult to find a solution that will cope with all possibilities.

Would it be possible to add an extra field to the table so that you can store the core code and the suffix separately? A field in a relational database should only hold one item of information and it's usually a bad sign when you find yourself having to write code to break a field down into small components on the fly.

Geoff Franklin

http://www.alvechurchdata.co.uk