CODEWITHSUNDEEP
sqlms-access
Jmorte13
Jmorte13

Reputation: 29

SQL Access count duplicates without ID column

I have tried to make a query that created a column that incremented each time a value in a column was there more than once.

So I have:

Col1

99999999
99999999
88888888
88888888
88888888
77777777

And would like result:

  Col1      Col2
99999999      1
99999999      2
88888888      1
88888888      2
88888888      3
77777777      1

I had this SQL that could do it when I have an ID field:

SELECT
    m.PN,
    m.CLASS,
    m.ZAHL,
    Count(M2.id) AS IncByPN
FROM Table1 AS m
    INNER JOIN Table1 AS m2 ON m2.PN = m.PN AND m2.ID <= m.ID
WHERE m.CLASS
GROUP BY m.ID, m.CLASS, m.PN, m.ZAHL
ORDER BY m.ID

Now I have a table where there is no ID column in it. How can I do it?

Upvotes: 1

Views: 59

Answers (2)

Gustav
Gustav

Reputation: 55951

.. make a query that created a column that incremented each time a value in a column was there more than once.

You can't use a query for that, as records have no order. A table is not a spreadsheet.

But in VBA you can open a recordset with the orders. This will "fix" the records for this session. Then loop through the records and assign your count for each record.

Upvotes: 0

sagi
sagi

Reputation: 40491

You can use a correlated query instead of a join:

SELECT
    m.PN,
    m.CLASS,
    m.ZAHL,
    (SELECT Count(M2.id) + 1 AS IncByPN FROM Table1 m2
     WHERE m.PN = m2.PN AND m2.ID <= m.ID)
FROM Table1 m
ORDER BY m.ID

Upvotes: 1

Related Questions