473,320 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Update Query which counts the occurrence of duplicate records

How can I query an existing table and update a field in each record in the
table with the occurrence count of each record e.g. update field to 1 (=
first record occurrence), update field to 2 for 2nd record occurrence i.e.
first duplicate record), update the field to 3 for the 3rd record occurrence
i.e. 2nd duplicate record

Example for a duplicated record (field with value ABCD)
Rec 1 ABCD 1
Rec 2 ABCD 2 (first duplicate)
Rec 3 ABCD 3 (2nd duplicate)
Rec 4 ABCD 4 (3rd duplicate)

Any help appreciated.
Thanks,
Bob
Nov 12 '05 #1
1 4292
Bob,

Here's the test table, entered the text, the cnt fields were blank before I
ran the code below. Not sure why the later records got the earlier numbers.
If you want the first record to get cnt=1, maybe there's an id field you can
add to the sort to make this happen. You'll need a reference to Microsoft
DAO 3.6 Object Library.
textfld cnt
ABCD 4
ABCD 3
AB 2
ACD 3
ACD 2
AB 1
ABCD 2
DEF 1
EF 2
ACD 1
EF 1
ABCD 1
Public Function AddCnt() As Boolean
Dim num As Integer
Dim savetext As String
Dim firstrec As Boolean
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("select textfld, cnt from tblTextCnt order
by textfld")

firstrec = True
If Not rst.EOF Then
rst.MoveFirst
While Not rst.EOF
rst.Edit
If firstrec Then
num = 1
savetext = rst.Fields("textfld")
firstrec = False
Else
If rst.Fields("textfld") = savetext Then
num = num + 1
Else
savetext = rst.Fields("textfld")
num = 1
End If
End If
rst.Fields("cnt") = num
rst.Update
rst.MoveNext
Wend
End If

rst.Close
End Function

HTH Linda
"Robert" <ro*******@bigpond.com> wrote in message
news:d7******************@news-server.bigpond.net.au...
How can I query an existing table and update a field in each record in the
table with the occurrence count of each record e.g. update field to 1 (=
first record occurrence), update field to 2 for 2nd record occurrence i.e.
first duplicate record), update the field to 3 for the 3rd record occurrence i.e. 2nd duplicate record

Example for a duplicated record (field with value ABCD)
Rec 1 ABCD 1
Rec 2 ABCD 2 (first duplicate)
Rec 3 ABCD 3 (2nd duplicate)
Rec 4 ABCD 4 (3rd duplicate)

Any help appreciated.
Thanks,
Bob

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Karaoke Prince | last post by:
Hi There, I have an update statement to update a field of a table (~15,000,000 records). It took me around 3 hours to finish 2 weeks ago. After that no one touched the server and no...
4
by: Rick | last post by:
Suppose you have a table in which one of the fields can have any number of the same values. Is there a way to select on that field for only those records where there is a single occurrence of that...
2
by: MVA | last post by:
Hi all I have a database where in a table (tbl_Members), there are 2 date fields (DateOfBirth and DateJoined), and also 2 fields which upon entering the data in the form, it automatically works...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
1
by: G Gerard | last post by:
Hello I am trying to update a table (TableB) using records from a second table (TableA)
4
by: FangQ | last post by:
hi I am very new to mysql. I have a question about using the "on duplicate update" clause with insert command. my table "data" has two columns, field1 and field2, where field1 is the index...
1
by: dee | last post by:
I have a table 'LeadHistory' which has among others, the following fields. Salesman(Text) SalesmanAssmntDate(Date/Time) Disposition(Text) I also have a table 'LookUpSalesman' which has among...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
3
by: ringer | last post by:
I am trying to add a functionality into a db I use for my checkbook that will help me plan for and save money for future large expenses. Into a table called tblFutureTransactions I want to enter...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.