Alpha SQL MAX(id) increment issue

[tonybury via AccessMonster.com]

I've been searching the forums for a while now and cannot seem to find
anything on this. I am working with the MAX statement in SQL. I am using this
to increment my shipto addresses by adding a letter after the number.

example: Customer H12345 would have a first shipto address of H12345A until
it reaches Z.

Problem:
I am now in need of having more than 26 shipto addresses.

Addressing the Issue:
I am attempting to just add another letter onto the end of the shipto address.


example: H12345AA

I was hoping the max sql statement would notice the extra letter in the list
but it is seeing Z as the max still. I am hoping there is an easier way of
attacking this than what I am thinking of.

I am getting to the point of dumping the data into a result set or something
then working through them with a loop. Although I think this is inefficient
and am trying to find a better way.

Any suggestions out there??

Regards,

Tony

 

[mscertified]

I would have another column which contains just a numeric value. Use the MAX
function on this column and then increment your alpha column.

-Dorian

 

[John W. Vinson]

I've been searching the forums for a while now and cannot seem to find
anything on this. I am working with the MAX statement in SQL. I am using this
to increment my shipto addresses by adding a letter after the number.

example: Customer H12345 would have a first shipto address of H12345A until
it reaches Z.

Problem:
I am now in need of having more than 26 shipto addresses.

Addressing the Issue:
I am attempting to just add another letter onto the end of the shipto address.


example: H12345AA

Eh?

This is a classic example of why you should always make fields "atomic".

You're trying to store two different pieces of information - a customer ID and
a sequence letter - in one field.

If you have a one (customer) to many (addresses) relationship, model it as a
one to many relationship! The Address table should have a customerID (H12345
say) as a link to the customers table, and an AddressNo (I'd use an integer
number, though you could use text if you really want to) as a separate field
incremented when a new record is added.

You can concatenate these for display purposes if you want, but storing the
sequence in the ID field is simply making your job harder for no benefit.

John W. Vinson [MVP]

 

[tonybury via AccessMonster.com]

John,

I agree with you on this matter. Unfortunately I am connecting into and
working with a database that I cannot modify. So I have to work with the
limitations of that database. I have actually given up on taking the time to
assign AA through AZ and just did this the easier way and assigned the values
as ZA though ZZ. That way I don't have to worry about the max function
limitations. Also I now don't have to worry about making a looping function
to determine the proper next address to use.

Thank you.

Regards,

Tony

I've been searching the forums for a while now and cannot seem to find
anything on this. I am working with the MAX statement in SQL. I am using this

[quoted text clipped - 10 lines]

example: H12345AA

Eh?

This is a classic example of why you should always make fields "atomic".

You're trying to store two different pieces of information - a customer ID and
a sequence letter - in one field.

If you have a one (customer) to many (addresses) relationship, model it as a
one to many relationship! The Address table should have a customerID (H12345
say) as a link to the customers table, and an AddressNo (I'd use an integer
number, though you could use text if you really want to) as a separate field
incremented when a new record is added.

You can concatenate these for display purposes if you want, but storing the
sequence in the ID field is simply making your job harder for no benefit.

John W. Vinson [MVP]