Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an Access 2000 table with 10 fields. I want to compare the 2 column and update the third one.

To elaborate :

keyfield  cno     entno      uno
(Text)    (Text)  (int)      (int)
1101     100       1         1*       
1101     100       1         2*
1101     100       1         3*
1101     100       2         1*
1101     100       2         2*

These values to be updated for following condition:

If keyfield = 1101
        and csno = 100 
            and entno = 1
   uno should be 1, 2, 3 
if entno changes then uno should start with 1
again if csno changes then uno should start 1
and if keyfield changes then uno should start with 1
share|improve this question
(1) From your pseudo-code it looks like you actually want to compare 3 columns and update a fourth one. Is that correct? (2) You apparently have 6 other columns. Should any of those other columns be used for sorting the records in each "uno" sequence, or can the "uno" sequence numbers be applied to the rows arbitrarily within each group? (3) Are you looking for for a pure SQL solution, or would you consider a solution that involved VBA (procedural) code? –  Gord Thompson Apr 20 '13 at 18:39
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.