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
|
|||
|
|||
Access 2000, autonumber fields
How do I set up a field with an autonumber that starts with a number other
than 1? |
#2
|
|||
|
|||
Zyberg74 wrote:
How do I set up a field with an autonumber that starts with a number other than 1? BEEP BEEP Autonumber misuse alert! I suggest you may not want to use Autonumber for that use. Autonumbers are designed to provide unique numbers. It in not designed to provide numbers in order and for a number of reasons may not do so. As a result using them in any application where the user sees the numbers is likely to end up with confusion. There are other ways of providing the numbers you want depending on the particual application. ~~~~ From the help file: Change the starting value of an AutoNumber field For a new table that contains no records, you can change the starting value of an AutoNumber field whose NewValues property is 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. If your original table contains property settings that prevent Null values in fields, you must temporarily change those properties. These settings include: The Required field property set to Yes The Indexed field property set to Yes (No Duplicates) A field and/or record ValidationRule property that prevents Null values in fields 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. In Datasheet view, enter a value in the Number field of the temporary table that is one (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? Create a query that contains the table whose records you want to append to another table. How? In the Database window, click Queries under Objects, and then click New on the Database window toolbar. In the New Query dialog box, click Design View, and then click OK. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with. Double-click the name of each object you want to add to the query, and then click Close. Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order. To view the query's results, click View on the toolbar. In query Design view, click the arrow next to Query Type on the toolbar, and then click Append. The Append dialog box appears. In the Table Name box, enter the name of the table you want to append records to. Do one of the following: If the table is in the currently open database, click Current Database. If the table is not in the currently open database, click Another Database and type the path of the database where the table is stored or click Browse to locate the database. You can also specify a path to a Microsoft FoxPro, Paradox, or dBASE database, or a connection string to an SQL database. Click OK. Drag from the field list to the query design grid the fields you want to append and any fields you want to use for setting criteria. If all the fields in both tables have the same names, you can just drag the asterisk (*) to the query design grid. However, if you're working in a database replica, you'll need to add all the fields instead. If you have a field with an AutoNumber data type, do one of the following: Add AutoNumber values automatically To have Microsoft Access add AutoNumber values automatically, don't drag the AutoNumber field to the query design grid when you create the query. With this method, Access appends records and automatically inserts AutoNumber values. The first record appended has a value that is one larger than the largest entry that was ever entered in the AutoNumber field (even if the record that contained the largest AutoNumber value has been deleted). Use this method if the AutoNumber field in the table you're appending to is a primary key, and the original table and the table you're appending to contain duplicate AutoNumber values. Keep the AutoNumber values from the original table To keep the AutoNumber values from the original table, drag its AutoNumber field to the query design grid when you create the query. If the fields you've selected have the same name in both tables, Microsoft Access automatically fills the matching name in the Append To row. If the fields in the two tables don't have the same name, in the Append To row, enter the names of the fields in the table you're appending to. In the Criteria cell for the fields that you have dragged to the grid, type the criteria on which additions will be made. To preview the records that the query will append, click View on the toolbar. To return to query Design view, click View on the toolbar again. Make any changes you want in Design view. Click Run on the toolbar to add the records. Delete the temporary table. Delete the record added by the append query. If you had to disable property settings in step 1, return them to their original settings. When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value one (1) greater than the value you entered in the temporary table. -- Joseph E. Meehan 26 + 6 = 1 It's Irish Math |
#3
|
|||
|
|||
If you use an Append query to add the value 1 less than you want, Access
will then start from the number you desire. Details in: Set AutoNumbers to start from ... at: http://members.iinet.net.au/~allenbrowne/ser-26.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Zyberg74" wrote in message ... How do I set up a field with an autonumber that starts with a number other than 1? |
#4
|
|||
|
|||
"Allen Browne" wrote ...
If you use an Append query to add the value 1 less than you want, Access will then start from the number you desire. I assume we are talking about an incrementing integer. Access2000 format is Jet 4.0, therefore the seed and increment may be specified. For an autonumber that starts at 21 and increments in steps of 7 (21, 28, 35, ...) use: CREATE TABLE MyTest( MyID IDENTITY(21,7), MyDataCol INTEGER NOT NULL ); This is one of the newer Jet features so one must use the OLE DB provider (although I heard a rumour that Access2003 can be put into Jet 4.0 'mode' and use the newer syntax natively). Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Unable to compile Access 2000 mdb from Access 2003. | jlsunny | General Discussion | 2 | October 1st, 2004 05:33 PM |
Access 2000 compatibile with Office XP | TeeCee | General Discussion | 1 | September 9th, 2004 10:30 PM |
How; concatenate/merge fields Access 2000 | Crash Gordon® | General Discussion | 5 | July 16th, 2004 08:59 PM |
SQL Server 2000 Stored Procedures to MS Access 2000 Queries | CS | General Discussion | 4 | July 15th, 2004 03:27 AM |
Access 2000 .adp and SQL 2000 | Wayne H. | New Users | 0 | May 3rd, 2004 08:28 PM |