165 pts.
POSTED: Mar 5, 2009 6:49 PM (GMT)
It did help. I actually created the combination with the expression builder in a query using the “&” to join the fields.
Combining fields to create a unique ID in Microsoft Access
I have been working on a database for my research job.For our research purposes we are trying to create a unique identifier that tells us 4 things:
1) what study they are a part of, [this will be “QQQ” for EVERYONE]
2) what group they are in, [this will be a four digit number, such as 2222]
3) what they’re relationship is within that group, [this will be two digit number starting at 01, 02, 03, etc. ß text field so it doesn’t drop the leading 0], and
4) a random number (so we can distinguish individuals who have identical info for all of the above fields) [single or double digit number]
All of this information is available in separate fields already established in the database. However, I would like to create ONE field that combines them all together as one string. For example, their new identifier that encompasses all of this information will read: “RRR-2222-01-1” (with or without the dashes)
Is there a way I can do this? I am working in Access 2003 and am fairly new to the database world.
1) what study they are a part of, [this will be “QQQ” for EVERYONE]
2) what group they are in, [this will be a four digit number, such as 2222]
3) what they’re relationship is within that group, [this will be two digit number starting at 01, 02, 03, etc. ß text field so it doesn’t drop the leading 0], and
4) a random number (so we can distinguish individuals who have identical info for all of the above fields) [single or double digit number]
All of this information is available in separate fields already established in the database. However, I would like to create ONE field that combines them all together as one string. For example, their new identifier that encompasses all of this information will read: “RRR-2222-01-1” (with or without the dashes)
Is there a way I can do this? I am working in Access 2003 and am fairly new to the database world.
ASKED:
Mar 4, 2009 6:53 PM GMT
UPDATED:
March 5, 2009 6:49:28 PM GMT
RELATED QUESTIONS
- School Project-setting up a network from the ground up for a real estate business that has 10 satellite offices. $125,000 budget.
- Finding Holes in Numerical Sequence
- Restricting access to a file share to certain computers
- Using combo box to populate a sub form which is used to create a new record
- Enterprise Solution
- Auto-Populate an Access form using a table of values
- CS1000 end of dial string
- Pros & cons to have a typical star schema but with only one fact value field and a thousand attribute names in one of the dimension tables?
- To increase number and combine data using sql
- VALIDATE 9 DIGIT ROUTING NUMBER
Answer Wiki:
Hi,
I would create a new text field for your unique ID number. Then use an update query to update this field. Put into the Update To row the following: [Field1] & "-" & [Field2] & "-" & [Field3] & "-" & [Field4]. For a random number to be the last field used, I would create an autonumber field. I hope this helps.
Dustin
Instead of creating a random number use the key for the record as the fourth field.
Dick P
I would create a new text field for your unique ID number. Then use an update query to update this field. Put into the Update To row the following: [Field1] & "-" & [Field2] & "-" & [Field3] & "-" & [Field4]. For a random number to be the last field used, I would create an autonumber field. I hope this helps.
Dustin
Instead of creating a random number use the key for the record as the fourth field.
Dick P
To see other answers submitted to the Answer Wiki View Answer History.
