CODEWITHSUNDEEP
ms-accessvbams-access-2007
user3294824
user3294824

Reputation: 1

MS Access counting duplicate row entries and returning value to column

Just started using Access so I'm not sure of the best way to do this. My table looks something like this:

UNIQUE_ID EVT_ID (DESIRED COLUMN)

1 1000 6
2 1000 6
3 1000 6
4 1000 6
5 1000 6
6 1000 6
7 1001 3
8 1001 3
9 1001 3

Each instance of EVT_ID can contain anything from 2-30 UNIQUE_IDs.

I would like to create a new column in the database which contains the number of UNIQUE_IDs in a given EVT_ID. I'd ideally like to do this using VBA as that's where I'm more comfortable but would be happy with any solution.

Upvotes: 0

Views: 813

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123484

In Access, open the table in Design View and add the field that will hold the new values. Save the changes and close the table, and then you can use the following VBA code to update the values

Option Compare Database
Option Explicit

Sub UpdateDesiredColumn()
    Dim cdb As DAO.Database, rst As DAO.Recordset, qdf As DAO.QueryDef
    Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset( _
            "SELECT EVT_ID, COUNT(*) AS n FROM MyTable GROUP BY EVT_ID", _
            dbOpenSnapshot)
    Set qdf = cdb.CreateQueryDef("", _
            "PARAMETERS [prmCount] Long, [prmEVT_ID] Long;" & _
            "UPDATE MyTable SET DESIRED_COLUMN=[prmCount] WHERE EVT_ID=[prmEVT_ID]")
    Do Until rst.EOF
        qdf!prmCount = rst!n
        qdf!prmEVT_ID = rst!EVT_ID
        qdf.Execute dbFailOnError
        rst.MoveNext
    Loop
    Set qdf = Nothing
    rst.Close
    Set rst = Nothing
    Set cdb = Nothing
End Sub

Upvotes: 1

Related Questions