Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query to Assign number based on number of occurances

Status
Not open for further replies.

firebirdta84

Technical User
Aug 23, 2007
56
US
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
 
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
 
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
 
To find the duplicates, you can do:

Code:
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
 
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:
SQL:
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
 
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
 
It would be nice to share your solution here so others could benefit from your experience, too.

Have fun.

---- Andy
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top