Suffix Field Values based on number of occurrences in Table

Question

I have a table in MS Access that has a field layout_desc. I need to create a query that changes each value in this field by adding how many times the value is repeated in the table.

For example:

Thank you.

Answer 1

Without a primary key:

Assuming that your table does not contain a primary key field by which to sort records using a domain aggregate function, one possible method is using Static variables in VBA.

• Open the VBA IDE using Alt+F11
• Insert a new Public Module Alt+I,M

• Copy the following basic code into the new Module:

  Function Occurrence(Optional strVal As String) As Long
      Static lngTmp As Long
      Static strTmp As String
      If strTmp = strVal Then
          lngTmp = lngTmp + 1
      Else
          lngTmp = 1
          strTmp = strVal
      End If
      Occurrence = lngTmp
  End Function
  

• In MS Access, create a new query with the following SQL, changing YourTable to the name of your table:

  update (select t.layout_desc from YourTable as t order by t.layout_desc) q
  set q.layout_desc = q.layout_desc & occurrence(q.layout_desc)
  

With a primary key:

If your table were to include a primary key, say id, of Long Integer data type, you could use the domain aggregate function DCount in the following way:

update YourTable t
set t.layout_desc = t.layout_desc & 
dcount("*","YourTable","layout_desc = '" & t.layout_desc & "' and id <= " & t.id)