Query to Assign number based on number of occurances

[firebirdta84]

Hey guys,

Need a little bit of help with this one. I have a table (tblCases) containing about 100,000 records. Each record has a ten digit number in the field called Case_Number. This data in this field is not unique; a single Case Number can exist in multiple records. I also have a field in tblCases called ManifestID. This field is blank. What I need is to automatically update ManifestID to equal the Case Number plus a two digit number at the end that would be unique for each matching case number. So, for example:

CASE NUMBER MANIFEST ID
-----------------------------
1123456789 112345678901
1123456789 112345678902
1398774809 139877480901
1123456789 112345678903

The unique 2 digits at the end must only be unique to the Case Number; i.e. the two digits can be the same if the case number is different.

Any help would be appreciated.

Thanks!
Joe

 

[Andrzejek]

field in tblCases called ManifestID. This field is blank" - at this time, do you have any field (ID?) that is unique for each record?

Have fun.

---- Andy

 

[firebirdta84]

Yes, there is an ID field that is unique. I thought about appending the ID to the end of the case number, but I need the unique 2 digits to repeat for every case number, starting at 01.

Thanks
Joe

 

[Andrzejek]

To find the duplicates, you can do:

SELECT Case_Number, 
 COUNT(Case_Number) AS NumOccurrences
FROM tblCases
GROUP BY Case_Number
HAVING ( COUNT(Case_Number) > 1 )

Based on this request you will know which records are how many times in your table.
That's a good place to start...

Have fun.

---- Andy

 

[dhookom]

It isn't clear if the Manifest and Case number fields are numeric or text. I assumed they might be text, if so you can try an update query like:

UPDATE TT_tblCases SET [MANIFEST ID] = 
[CASE NUMBER] & Format(DCount("*","TT_tblCases","[CASE NUMBER]='" & [CASE NUMBER] & "' AND ID <=" & [ID]),"00")
WHERE Len([CASE NUMBER])<=10;

If they are both numeric, you can do something similar however multiply the case number by 100 and add the DCount().

Duane
Hook'D on Access
MS Access MVP

 

[firebirdta84]

Thanks Andy. The duplicate part I understand, I'm just not sure how I would go about via VBA coding updating all the records with the correct value. Would it be a loop where it starts with the first record, sees how many dupes there are, sees the highest number already assigned to the ManifestID, and then increments that by one?

Joe

 

[firebirdta84]

I figured it out...thanks!

 

[Andrzejek]

It would be nice to share your solution here so others could benefit from your experience, too.

Have fun.

---- Andy