I am working on a database for a small law firm. I want to create a unique identifier field from clients surnames. I want the field to start from 1000 if the surnames begin with A, B, and C and from 2000 if they begin with D, E, and F and so on. can that be done?
Need help
- Thread starter BoxRH
- Start date
The primary key for a file can be more than one field. What if you make the first field your prefix ("1" for A, B or C, "2" for D, E, F, etc), then make the second field an Access autonumber field. (Hint: Make the autonumber more than three positions...)
This will give you unique ids, but it will not give you a record A00001 (for John Adams) and a record B00001 (for John Doe); the autonumbers will be unique in the entire file. If that's OK with you, this will be the easiest to maintain in Acces.
What does the prefix do for you anyway? You can easily just make the key an Autonumber, then calculate a grouping like that whenever you'd want to use it. In a small database, there's no need to break records out with a prefix. (And what are you going to do when someone [legally
] changes their name?)
This will give you unique ids, but it will not give you a record A00001 (for John Adams) and a record B00001 (for John Doe); the autonumbers will be unique in the entire file. If that's OK with you, this will be the easiest to maintain in Acces.
What does the prefix do for you anyway? You can easily just make the key an Autonumber, then calculate a grouping like that whenever you'd want to use it. In a small database, there's no need to break records out with a prefix. (And what are you going to do when someone [legally
] changes their name?)Code for which option?
If you want to do your groups on the fly, you would create a new field in your query.
There are several ways to fill it:
* In the "Field" row in your query, use an Iif, long but not impossible:
GroupCode:Iif(Left$([MYFILE]![Name_Last],1) In ("A","B","C"),"1",Iif(([MYFILE]![Name_Last] In ("D","E","F"),"2", ......
* Build a little lookup table, with two fields. One is the field you are looking (the first letter of the last name), the other is the group code you will return. You'll have 26 table entries in the first field, and it should be indexed (A through Z). Do a Dlookup to find the code. This is nice if the user wants to be able to change the codes later, you can just have them maintain the table.
*Build a function that you would call at that point, and the function can return the grouping. This is useful if it takes a lot of messy code to get your groups. You could use a case structure here very nicely.
If you want to do your groups on the fly, you would create a new field in your query.
There are several ways to fill it:
* In the "Field" row in your query, use an Iif, long but not impossible:
GroupCode:Iif(Left$([MYFILE]![Name_Last],1) In ("A","B","C"),"1",Iif(([MYFILE]![Name_Last] In ("D","E","F"),"2", ......
* Build a little lookup table, with two fields. One is the field you are looking (the first letter of the last name), the other is the group code you will return. You'll have 26 table entries in the first field, and it should be indexed (A through Z). Do a Dlookup to find the code. This is nice if the user wants to be able to change the codes later, you can just have them maintain the table.
*Build a function that you would call at that point, and the function can return the grouping. This is useful if it takes a lot of messy code to get your groups. You could use a case structure here very nicely.
