If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Assigning a Specified Unique ID
I have a table already populated with distinct records
and I need to add a field where I can assign a unique ID. I want the unique ID for the first record to read F007708604 and then go from there (F007708605, 606, etc.) How do I go about getting these ID's into my table? Any ideas/suggestions would be greatly appreciated. Thanks so much in advance :-) |
#2
|
|||
|
|||
Assigning a Specified Unique ID
Here's the Help topic on how to do this.
Yes, believe it or not, it was actually in Help, Change the starting value of an incrementing AutoNumber field For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number. Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change. How? In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field. Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change. How? Note If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings. Delete the temporary table. Delete the record added by the append query. If you had to disable property settings in step 3, return them to their original settings. When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table. Note If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51. |
#3
|
|||
|
|||
Assigning a Specified Unique ID
Hi Rose,
I had found the topic in help, but I couldn't get it to run right. I already have records in 1 table. When I append the new id field, it only auto numbers correctly for any new record that I add in- I need to add a unique field to the records already in the table- Any other advice? Thanks! -----Original Message----- I have a table already populated with distinct records and I need to add a field where I can assign a unique ID. I want the unique ID for the first record to read F007708604 and then go from there (F007708605, 606, etc.) How do I go about getting these ID's into my table? Any ideas/suggestions would be greatly appreciated. Thanks so much in advance :-) . |
#4
|
|||
|
|||
Assigning a Specified Unique ID
I'm sure the more brilliant of the Access help guys could
come up with a more elegant solution. But since this is a one-shot, a possibility that springs to mind is to a) 'port the table to Excel b) autofill the field ID column (by typing in the first two values, highlighting them both, and dragging down the handle in the bottom right of the cells) c) export the sheet back to Access Not pretty, but it would work. |
#5
|
|||
|
|||
Assigning a Specified Unique ID
I would strongly discourage using an AN field to get a meaningful IDs
like this. Even though it may seem to work, AN is not editable and is not guaranteed to be in any order or continuous. It is only "guaranteed" to be unique (although if you read the NG, you will see posts that claim duplicate AN in corrupted databases). You should write a little piece of code that will generate your own automatic, incrementing Ids. You can get a sample little db that will show how to do this from here http://www.ainaco.com/access/db_samples/anum_cboxes.zip and more help from others: http://groups.google.com/groups?hl=e...ases.ms-access Good luck, Pavel M Johnson wrote: I have a table already populated with distinct records and I need to add a field where I can assign a unique ID. I want the unique ID for the first record to read F007708604 and then go from there (F007708605, 606, etc.) How do I go about getting these ID's into my table? Any ideas/suggestions would be greatly appreciated. Thanks so much in advance :-) |
#6
|
|||
|
|||
Assigning a Specified Unique ID
"M Johnson" wrote in
: I want the unique ID for the first record to read F007708604 and then go from there (F007708605, 606, etc.) How do I go about getting these ID's into my table? I think this is one of those rare occasions where iterating a recordset might be the best way to do things. There is an SQL solution, but it's not trivial and debugging and testing would probably take longer than a quick- and-dirty VBA sub. First of all, I am not clear about your data needs. What about this "F" on the front: does it have any meaning? will it ever change? And the starting number is both very precise and very large -- is there some special significance to the 007708 part, or is this really going to count up eight million records? I have a strong suspicion that your business needs can be easier met by using a the .Format property of the textboxes on your forms and reports. Some random assumptions: the "F" is significant and needs to be in a separate field, called "SerialCode"; the first four digits "0077" are fixed and can be inserted at display time, leaving a number "SerialNumber" that increments from 8605. If your situation is different, then you can make changes as needed. First, use the UI to insert a Text(1) field for the SerialCode and the SerialNumber as a Long Integer. Leave both as NOT REQUIRED, and default values of NULL for now. You can't set them as PK yet, because they don't have unique values. Next run a bit of VBA to fill them like this: strSQL = "SELECT SerialCode, SerialNumber " & vbNewLine & _ "FROM MyTable" & vbNewLine & _ "ORDER BY SomeSortingCriterion;" Set rs = db.OpenRecordset(strSQL, dbOpenDynaset) dwNumber = c_dwStartingNumber ' = 8605 but use a const Do While Net rs.EOF rs.Edit rs!SerialCode = "F" rs!SerialNumber = dwNumber rs.Update dwNumber = dwNumber + 1 rs.MoveNext Loop rs.Close Now go back to the table design, set the two new fields to Required TRUE, and combine them into the PK. Finally, you'll need some VBA on your data entry or record creation forms to allocate a new SerialNumber. Check Google for "Access Custom Autonumber" for a large number of solutions that may fit your need. When you need to display the number, you should use a query that includes soemthing like the following: SELECT SerialCode & "0077" & FORMAT(SerialNumber, "00000") AS UniqueID, etc, etc... Hope that helps Tim F |
Thread Tools | |
Display Modes | |
|
|