Assigning numbers to duplicates

S

Sharon123

I have some several fields that have duplicate Patient_ID and I would like to
count those duplicates and assign them a number for example.
Patient_ID Radiology
123 MRI
123 CT
123 MRI
127 CT
127 CT
138 CT
I have been using the Total Count in the query but it summarizes the count
for all Patients what I need is this:
Patient_ID Radiology Count_Per_Patient
123 MRI 1
123 CT 2
123 MRI 3
127 CT 1
127 CT 2
138 CT 1
Thanks for advance for anyone who can help me with this. I have been breaking
my head for days trying to figure it out.
 
A

Allen Browne

Like any table, you must have a primary key in this table to achieve what
you need.

Without a primary key, there is no way to even order the records in any
reliable way.

If you add a primary key, you can sort on that field, and you can use a
subquery to give you the numbering you want. Assuming a primary key named
ID, and a table named Table1, you would type an expression like this into
the Field row in query design:
(SELECT Count(ID) AS CountOfID
FROM Table1 AS Dupe
WHERE Dupe.Patient_ID = Table1.Patient_ID
AND Dupe.Radiology = Table1.Radiology
AND Dupe.ID <= Table1.ID)

For an explanation, see:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html
 
Top