Group by Starting Letters 1

[Dave177]

Hi there,

Is it possible to group by letter intervals?
For example, if there was a table [tblRepsGrouping] with data as follows as follows:

SurnameStartLetters RepNumber
A to BU 1
BT to DE 2
DD to GU 3
GV to IM 4
etc.

So with the first 2 letters of the field [Surname] from [tblMembers] a query could be made to identify which Rep was responsible for each person?

Thanks for any help.

Dave

 

[PHV]

I'd use a table like this:[tt]
StartLetters EndLetters RepNumber
<space> BT 1
BU DD 2
DE GU 3
GV IL 4
...[/tt]
Then, the SQL code:
SELECT M.Surname, R.RepNumber
FROM tblMembers M, tblRepsGrouping R
WHERE Left(M.Surname, 2) Between R.StartLetters And R.EndLetters

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dave177]

Brilliant, thanks a lot!

D