Duplicate Entry Woes

[MadCat]

I have a table that has (to simplify things) two fields.

[Patient_No] [ID]
BA4206 1067404
BA4206 1067405
BG1013 1067545
BG1013 1067546
BG1013 1067547
BG1111 1078432

I want to be able to count the number entries where the Patient_No is the same and output the query like this:

[Patient_No] [ID] [COUNT]
BA4206 1067404 1
BA4206 1067405 2
BG1013 1067545 1
BG1013 1067546 2
BG1013 1067547 3
BG1111 1078432 1


Hopefully this is just a simple query but for some reason i cannot get it to work.

Any help is much appreciated.

Thanks in advance

 

[strep21]

Using tblInput for your table name :

SELECT tblInput.Patient_No, tblInput.ID, DCount("ID","tblInput","Patient_No= '" & [Patient_no] & "'") AS NR
FROM tblInput;

 

[MadCat]

THanks for the prompt response. I've had a look at this and it gives me a count yes, but instead of incrementing on each entry it gives me the total count in each field.

i.e.

[Patient_No] [ID] [COUNT]
BA4206 1067404 2
BA4206 1067405 2
BG1013 1067545 3
BG1013 1067546 3
BG1013 1067547 3
BG1111 1078432 1

I really need this to count up from 1 to however many duplicates there are.

Thanks very much for helping though.

 

[strep21]

Sorry, I misunderstood your question.


Insert a new module.
On the top of that module, put :
Dim lngGroup
Dim lngGroupIncrement As Long

Then put this function in the module :

Public Function GroupIncrement(Group) As Long

If Group = lngGroup Then
lngGroupIncrement = lngGroupIncrement + 1
Else
lngGroupIncrement = 1
lngGroup = Group
End If
GroupIncrement = lngGroupIncrement

End Function
Change the query into :
SELECT tblInput.Patient_No, tblInput.ID, Groupincrement([Patient_No]) AS [Group]
FROM tblInput;


Example attached. Use query 2

 

[MadCat]

Brilliant!

Thats great. worked perfectly, but then you already knew that from the example you supplied. Thank you so much for this.

Cheers

 

[Pat Hartman]

A code module is overkill since the Count() function works fine:

Select [Patient_No], [ID] Count(*) As MyCOUNT
From YourTable
Group by [Patient_No], [ID];

 

[MadCat]

Pat,

That doesn't seem to accumulate the values for each Patient_No. It still gives 1 for each record as the count. i need it to count 1, 2, 3 if there are three entries for the same Patient_No with different ID.

 

[Pat Hartman]

Sorry, just remove the ID.

Select [Patient_No] Count(*) As MyCOUNT
From YourTable
Group by [Patient_No];