Update Query Counting occurrences in a field and Sequential numbering the occurs

  • Thread starter Hoardling via AccessMonster.com
  • Start date
H

Hoardling via AccessMonster.com

Hello
I have a table with a column named "Policy". Unfortunately, it is an
imported table without a primary key. My problem is that I need to
distinguish this field. There are duplicate Policy numbers and other columns
with the same or different records For Example:
Policy LName FName etc.... JOBType
12345 Eistein Albert 14587
12345 Eistein Albert 14588
55555 Edison Thomas 14587
99999 Washington George 12222
99999 Washington George 14587

What I would like to do is add a column after Policy that will sequentially
count Policy's duplicates and assign a number to it. This way I can use
Policy and the Policy Counter together to form a primary key. Please take
note that the etc.... fields will not work as primary keys or distinguishers
due to blanks within some of the fields. Policy will never have blanks, but
duplicates do happen. I would like this to be an Access Update Query that
INSERTS a column. .

So it looks like
Policy Policy Counter LName FName etc.... JOBType
12345 1 Eistein Albert
14587
12345 2 Eistein Albert
14588
55555 1 Edison Thomas
14587
99999 1 Washington George 12222
99999 2 Washington
14587
99999 3 Washington George


This is my first time posting so please pardon me for any bad form.
If someone knows an answer that would be GREAT, but if not a general push in
the right direction would be great also.
Thanks
 
M

Marshall Barton

Hoardling said:
I have a table with a column named "Policy". Unfortunately, it is an
imported table without a primary key. My problem is that I need to
distinguish this field. There are duplicate Policy numbers and other columns
with the same or different records For Example:
Policy LName FName etc.... JOBType
12345 Eistein Albert 14587
12345 Eistein Albert 14588
55555 Edison Thomas 14587
99999 Washington George 12222
99999 Washington George 14587

What I would like to do is add a column after Policy that will sequentially
count Policy's duplicates and assign a number to it. This way I can use
Policy and the Policy Counter together to form a primary key. Please take
note that the etc.... fields will not work as primary keys or distinguishers
due to blanks within some of the fields. Policy will never have blanks, but
duplicates do happen. I would like this to be an Access Update Query that
INSERTS a column. .

So it looks like
Policy Policy Counter LName FName etc.... JOBType
12345 1 Eistein Albert
14587
12345 2 Eistein Albert
14588
55555 1 Edison Thomas
14587
99999 1 Washington George 12222
99999 2 Washington
14587
99999 3 Washington George
Click to expand...


You need to have some field that can be used to uniquely
determine which policy record gets a 1, which gets a 2, etc.

Possible the JobType field can be used for this purpose?
If so, you could use:

UPDATE thetable
SET PolCnt = DCnt("*", "thetable", "Policy=" & Policy
& " And JobType <=" & JobType)
 
Top