HELP!!! conditionally increment text string query

[newtothis]

I have a simple select query , and I want to add a field that will take the value from another field and increment it based on a condtion.

i know i need a case statement but not sure how to go about this ..
column 4 is my desired result

i want to increment the text string from column 3 based on column 3 and restart the increment based on column 1
values of A would increment in the following manner 01,11,21,31,41 etc
values of O would increment 02,12,22,32,42,52,62
i'd also like the option that if a new value were introduced to increment it
values of x would increment 80,81,82,83,84

i'm new to this and appreciate any direction and help you can give!

Example data .. apples and oranges ..
column1 column2(name) column3(type) column4
mary braeburn A 01A
mary blood O 02A
tom cortland A 01A
tom fuji A 11A
tom navel O 02O
tom mcintosh A 21A
tom golden delicious A 31A
tom Valencia O 12O
tom Sunkist O 22O
fred braeburn A 01A
fred blood O 02O
fred cortland A 11A
james fuji A 01A
james navel O 02O

 

[newtothis]

'http : // img849.imageshack.us / img849 /7748 /ryph.png ''

 

[JHB]

You can use an UDF + a recordset to get the required result.
I've made a small example for you in the attached database.
Run the only query in it.