Search code examples
databasems-accesscountms-access-2016

Count rows which has the same ID and display on the table

This is the original table:

| ID | Card_No |
|----+---------|
| 1  | 6453671 |
| 1  | 8795732 |
| 1  | 9948495 |
| 2  | 7483009 |
| 2  | 1029001 |
| 3  | 7463094 |

Is it possible to make it like this? Which will be adding a calculated column the the original table?

| ID | Card_No | Total |
|----+---------|-------|
| 1  | 6453671 |   3   |
| 1  | 8795732 |   3   |
| 1  | 9948495 |   3   |
| 2  | 7483009 |   2   |
| 2  | 1029001 |   2   |
| 3  | 7463094 |   3   |

I'm using Microsoft Access, and I've tried code like this:

SELECT ID, COUNT (*) AS Total FROM Table GROUP BY ID

But I did not get the result I want.

Solution

  • First of all, saving the calculated value back into table is not only unnecessary but bad design.

    Options:

    1. build a report that counts records with an expression in textbox

    2. build aggregate query then another query joining aggregate query to the table

    3. DCount() domain aggregate function in query