Access (VBA) to create unique ID numbers for alike records
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
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
Last Comment
ASKER
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
ASKER
Gustav -- your solution works perfectly... in fact, it worked better than anticipated once I plugged it into my actual data set.
Thousand thanks!!
EEH
Thousand thanks!!
EEH
ASKER
Gustov -- you provided the perfect solution. Thank you very much for your assistance in this matter.
EEH
EEH
You are welcome!
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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.