Compare two records in a Table and update a field

[Adoli]

Hi everyone,

I'm not a VBA specialist but I know the basis. I just created an Access database (2007) and I need to write a VBA code and nothing is working .
I have one table that looks like this:

NUM Price Effective Date Occurrence
1 10 1/1/2012
1 12 2/3/2012
2 5 1/12/2012
3 6 4/5/2012
3 8 6/7/2012
3 9 8/7/2012
4 2 3/4/2012
5 11 8/9/2012
5 12 10/10/2012

i'm trying to populate the Occurence field based on those criteria:

if previous NUM = next NUM then occurence = "Effective Date 1"
if previous NUM = next NUM (again) then occurence ="Effective Date 2"
if previous NUM = next NUM (for the third time) then occurence ="Effective Date 3" and so on until previous NUM <> next NUM.
I need to do it until the EOF

at the end the table should look like this:

NUM Price Effective Date Occurrence
1 10 1/1/2012 Effective Date 1
1 12 2/3/2012 Effective Date 2
2 5 1/12/2012 Effective Date 1
3 6 4/5/2012 Effective Date 1
3 8 6/7/2012 Effective Date 2
3 9 8/7/2012 Effective Date 3
4 2 3/4/2012 Effective Date 1
5 11 8/9/2012 Effective Date 1
5 12 10/10/2012 Effective Date 2

does it make sense? can someone please help?

thanks for your help!!!!

 

[plog]

First, why does this have to be VBA? Second, you shouldn't store data you can determine from the other data. Third, the below SQL will produce the results you want:

SELECT YourTableNameHere.NUM, YourTableNameHere.Price, YourTableNameHere.[Effective Date], "Effective Date " & DCount("[NUM]","YourTableNameHere","[NUM]=" & [NUM] & " AND [Effective Date]<=#" & [Effective Date] & "#") AS Occurence
FROM YourTableNameHere;

Be sure to replace all instances of 'YourTableNameHere' with the name of your actual table.