CODEWITHSUNDEEP
sqlms-accessvba
Dawei Liu
Dawei Liu

Reputation: 9

Increment value when the field is the same

First, I'm sorry for the ambiguous title. Here's my problem : I'm using Access and I have this table :

+--------+-----------+
| PARENT | CHILD     |
+--------+-----------+
| JOHN   | TANIA     |
| JOHN   | ROBERT    |
| JOHN   | APRIL     |
| HELEN  | TOM       |
| HELEN  | GABRIELLE |
+--------+-----------+

And I would like to add a column like this with queries or VBA code :

+--------+-----------+---------+
| PARENT | CHILD     | LIST    |
+--------+-----------+---------+
| JOHN   | TANIA     | CHILD 1 |
| JOHN   | ROBERT    | CHILD 2 |
| JOHN   | APRIL     | CHILD 3 |
| HELEN  | TOM       | CHILD 1 |
| HELEN  | GABRIELLE | CHILD 2 |
+--------+-----------+---------+

I want to do this because at the end, I want to run a cross tab query. I'm only missing that last column to create that query.

  1. I tried to do it in a recordset, but my database starts bloating after a couple of rst.Update (I have 700k+ rows)

  2. I created a temporary table and used UPDATE queries but it just takes too much time.

I think there might be a SQL code that would do what I need, but I just can't figure it out. I hope you could help me, thanks :)

Upvotes: 0

Views: 114

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

You can do something like the below, but it would be much better with some sort of IDs:

SELECT Parent.PARENT, 
     Parent.CHILD, 
    (SELECT Count(*) 
     FROM Parent p 
     WHERE p.Parent=Parent.Parent 
     AND p.Child<=Parent.Child) AS ChildNo
FROM Parent
ORDER BY Parent.PARENT, Parent.CHILD;

Parent is the name of the table.

query result

Upvotes: 2

Related Questions