Access (VBA) to create unique ID numbers for alike records

ExpExchHelp
ExpExchHelp used Ask the Experts™
on
Experts:

I need some assistance with (potentially) a VBA function that **adds a unique ID number** to records.   If records are alike (based on various demographics), the same new ID number must be replicated.

In other words, qryTable1 should be turned into a "Make Table" (and using the VBA function) and then add **8 records** with each alike record (based on [Name] and [Age] to the **new ID" field.   Alternatively, qryTable2 should be turned into a "Make Table" (and using the VBA function) and then add **9 records** with each alike record (based on [Name] and [Age] and [Ethnicity] to the **new ID" field.

As a picture is worth a thousand words, I believe (hope) the additional information in the spreadsheet provides sufficient information.

Thank you in advance,
EEH
Example-Data.xlsx
Example-Data.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can use my RowNumber function in my article here:

Sequential Rows in Microsoft Access

It will create a row number for each unique combo of fields, say: [Name] & CStr([Age])

Please note, that a demo is for download.
ExpExchHelpAnalyst

Author

Commented:
Gustav:

Thank you for the prompt response.   I downloaded your zipped demo file... wow, I'm not entirely certain how to translate your code into my basic table.

Attached is my attempt using your function RowNumber...

In my case, Query 1 produces 8 records while Query 2 produces 9 records.

Somehow though, I would like all 12 records (either in the existing table or a new table) to have the "ID" number.  

How would this have to be accomplished in my sample database (see attached)?

EEH
Example-Data-with-Gustavs-Module.accdb
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
It could be:

SELECT 
    RowNumber([FirstName] & CStr([Age])) AS ID, 
    [01_tblExample].FirstName, 
    [01_tblExample].Age, 
    [01_tblExample].Ethnicity
FROM 
    01_tblExample
WHERE 
    RowNumber([FirstName] & CStr([Age])) <> RowNumber("","",True)
ORDER BY 
    [01_tblExample].FirstName, 
    [01_tblExample].Age, 
    [01_tblExample].Ethnicity;

Open in new window

EEno.PNGNote, there is an error in the recording of Ages for Michaels.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
the same new ID number must be replicated.
Erm .....
 that does not make sens to begin with!!

IDs are usually used as primary keys, so they must be unique.
Unless you're speaking about a foreign key, but in that case, you should name it accordingly.
ExpExchHelpAnalyst

Author

Commented:
Gustav -- your solution works perfectly... in fact, it worked better than anticipated once I plugged it into my actual data set.  

Thousand thanks!!

EEH
ExpExchHelpAnalyst

Author

Commented:
Gustov -- you provided the perfect solution.   Thank you very much for your assistance in this matter.

EEH
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial