Modules & VBA :: Conditionally Increment Text String Query

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

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

[Code]...