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
|
|||
|
|||
Auto Number
Is it possible to set an auto number in a table , which is the primary key,
to start with a defined number say...1000. Thanks |
#2
|
|||
|
|||
Auto Number
Christina
Access Autonumbers are intended to serve as unique row identifiers, and are generally unfit for human consumption. Given that, there's rarely a situation in which the user (or you) needs to be concerned with where it starts. Why do you care which number it starts at? Are you attempting to use that Autonumber to "mean" something? More info, please... Regards Jeff Boyce Microsoft Office/Access MVP "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
#3
|
|||
|
|||
Auto Number
While Jeff's correct that you shouldn't care about the value of AutoNumber
fields, I know that sometimes you have existing data, so you have to start at a higher number. See what Allen Browne has at http://www.allenbrowne.com/ser-26.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
#4
|
|||
|
|||
Auto Number
Thanks for the replies. I have a database that calculates payroll and stores
the data to run year end reports for taxes. ( This is not in the US.) So if a person starts using the database during the year, the previous records need to be entered, so that the data is complete at year end. I have a unique identifier, for the new records which is an auto number, and the primary key. I would like to set the number to start at say 7500. My knowledge is very basic, so I would need detail instructions on what to do. Sorry, I mean very basic, on what to do. thanks "Douglas J. Steele" wrote: While Jeff's correct that you shouldn't care about the value of AutoNumber fields, I know that sometimes you have existing data, so you have to start at a higher number. See what Allen Browne has at http://www.allenbrowne.com/ser-26.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
#5
|
|||
|
|||
Auto Number
I looked at the link. Please help. Where should I post the code.
Thanks "Christina" wrote: Thanks for the replies. I have a database that calculates payroll and stores the data to run year end reports for taxes. ( This is not in the US.) So if a person starts using the database during the year, the previous records need to be entered, so that the data is complete at year end. I have a unique identifier, for the new records which is an auto number, and the primary key. I would like to set the number to start at say 7500. My knowledge is very basic, so I would need detail instructions on what to do. Sorry, I mean very basic, on what to do. thanks "Douglas J. Steele" wrote: While Jeff's correct that you shouldn't care about the value of AutoNumber fields, I know that sometimes you have existing data, so you have to start at a higher number. See what Allen Browne has at http://www.allenbrowne.com/ser-26.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
#6
|
|||
|
|||
Auto Number
Hi Christina
We're not trying to be difficult here. Sure, it's possible to set your autonumber "seed" to start at 7500. Then you will get 7501, 7502, etc. But what happens when you want to go back and enter the old data? You won't be able to enter number 7499, and even if you were to try, you risk seriously corrupting your data. As Jeff put it, autonumbers are just a means for the database to uniquely identify a record, and they are not intended for human consumption. If your data has some natural way of being ordered, then you should add that value as a field. It sounds like your data needs to be ordered by date, so add a field named TransactionDate (or PayDate, or whatever) to store the date of the record. Then it doesn't matter what order they are entered, you will always be able to order the records by date. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... Thanks for the replies. I have a database that calculates payroll and stores the data to run year end reports for taxes. ( This is not in the US.) So if a person starts using the database during the year, the previous records need to be entered, so that the data is complete at year end. I have a unique identifier, for the new records which is an auto number, and the primary key. I would like to set the number to start at say 7500. My knowledge is very basic, so I would need detail instructions on what to do. Sorry, I mean very basic, on what to do. thanks "Douglas J. Steele" wrote: While Jeff's correct that you shouldn't care about the value of AutoNumber fields, I know that sometimes you have existing data, so you have to start at a higher number. See what Allen Browne has at http://www.allenbrowne.com/ser-26.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
#7
|
|||
|
|||
Auto Number
OK. Let me explain further. The first table where data is entered, before
the calculations are done has an ID, which is an autonumber and primary key. After I run my queries to calculate pay etc, the data is stored in a next table, using the same ID number form the 1st Table. I set the ID in that table to be primary key so that it does not duplicate, but that is set to number, not auto number, and it is into that table I would be keying the old data. It would be the first table I want to start the auto number at a high number, so that I can enter into the second table, past pay rolls, without duplicating the ID number. I hope you understand. Thanks "Graham Mandeno" wrote: Hi Christina We're not trying to be difficult here. Sure, it's possible to set your autonumber "seed" to start at 7500. Then you will get 7501, 7502, etc. But what happens when you want to go back and enter the old data? You won't be able to enter number 7499, and even if you were to try, you risk seriously corrupting your data. As Jeff put it, autonumbers are just a means for the database to uniquely identify a record, and they are not intended for human consumption. If your data has some natural way of being ordered, then you should add that value as a field. It sounds like your data needs to be ordered by date, so add a field named TransactionDate (or PayDate, or whatever) to store the date of the record. Then it doesn't matter what order they are entered, you will always be able to order the records by date. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... Thanks for the replies. I have a database that calculates payroll and stores the data to run year end reports for taxes. ( This is not in the US.) So if a person starts using the database during the year, the previous records need to be entered, so that the data is complete at year end. I have a unique identifier, for the new records which is an auto number, and the primary key. I would like to set the number to start at say 7500. My knowledge is very basic, so I would need detail instructions on what to do. Sorry, I mean very basic, on what to do. thanks "Douglas J. Steele" wrote: While Jeff's correct that you shouldn't care about the value of AutoNumber fields, I know that sometimes you have existing data, so you have to start at a higher number. See what Allen Browne has at http://www.allenbrowne.com/ser-26.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
#8
|
|||
|
|||
Auto Number
Hi Christina
I hope you understand. I'm afraid I *don't* understand... Firstly I can't see the requirement for two parallel tables. Why can't the data for the past payrolls be stored in the same table as the first data? Second, the order of an autonumber, by definition, should never be important. In fact, often an autonumber field is set to generate random numbers! Surely when you enter some payroll information there is some "natural" ordering field - a date or a week number or a pay period number or .... Thirdly, it sounds like you are storing all your payroll data in a single table, like a big spreadsheet. A relational database is NOT a spreadsheet! The best design for this should be a number of small tables - Employees, PayPeriods, TimeSheets, PayTransactions, etc. Can you please tell us about the structure of these two tables you have, so that we can better understand what you are trying to do? -- TIA :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... OK. Let me explain further. The first table where data is entered, before the calculations are done has an ID, which is an autonumber and primary key. After I run my queries to calculate pay etc, the data is stored in a next table, using the same ID number form the 1st Table. I set the ID in that table to be primary key so that it does not duplicate, but that is set to number, not auto number, and it is into that table I would be keying the old data. It would be the first table I want to start the auto number at a high number, so that I can enter into the second table, past pay rolls, without duplicating the ID number. I hope you understand. Thanks "Graham Mandeno" wrote: Hi Christina We're not trying to be difficult here. Sure, it's possible to set your autonumber "seed" to start at 7500. Then you will get 7501, 7502, etc. But what happens when you want to go back and enter the old data? You won't be able to enter number 7499, and even if you were to try, you risk seriously corrupting your data. As Jeff put it, autonumbers are just a means for the database to uniquely identify a record, and they are not intended for human consumption. If your data has some natural way of being ordered, then you should add that value as a field. It sounds like your data needs to be ordered by date, so add a field named TransactionDate (or PayDate, or whatever) to store the date of the record. Then it doesn't matter what order they are entered, you will always be able to order the records by date. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... Thanks for the replies. I have a database that calculates payroll and stores the data to run year end reports for taxes. ( This is not in the US.) So if a person starts using the database during the year, the previous records need to be entered, so that the data is complete at year end. I have a unique identifier, for the new records which is an auto number, and the primary key. I would like to set the number to start at say 7500. My knowledge is very basic, so I would need detail instructions on what to do. Sorry, I mean very basic, on what to do. thanks "Douglas J. Steele" wrote: While Jeff's correct that you shouldn't care about the value of AutoNumber fields, I know that sometimes you have existing data, so you have to start at a higher number. See what Allen Browne has at http://www.allenbrowne.com/ser-26.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
#9
|
|||
|
|||
Auto Number
I have a table for employee records, with the pay rate and other personal
data. I have a table (SALARIES PARTICULARS.)where the pay period, hours worked, any special deductions or allowances are entered. Each entry has an ID field data type being an autonumber-primary key. There are two other tables, with the Social Security and Income Tax Rates. A query is run to calculate payroll, calculating the Social security and Income tax and the result is then stored in another table, called SALARIES REGISTER, from which certain monthly and yearly tax and other reports are run.(this is not in the US)The ID is also brought forward to that table. It is stored in a field - data type -number and primary key. If the database is first used at mid year, then the old payroll data (not hours worked etc, just the payroll info) is entered directly into the SALARIES REGISTER table. Remember when the data is calculated and appended to the Salaries Register it brings over the Auto Number from the SALARIES PARTICULARS table. That is to ensure that the data is not duplicated. So I would like to leave a “space” if you could call it that , so that I can enter the old data starting from "1" Hope my situation is clearer . "Graham Mandeno" wrote: Hi Christina I hope you understand. I'm afraid I *don't* understand... Firstly I can't see the requirement for two parallel tables. Why can't the data for the past payrolls be stored in the same table as the first data? Second, the order of an autonumber, by definition, should never be important. In fact, often an autonumber field is set to generate random numbers! Surely when you enter some payroll information there is some "natural" ordering field - a date or a week number or a pay period number or ... Thirdly, it sounds like you are storing all your payroll data in a single table, like a big spreadsheet. A relational database is NOT a spreadsheet! The best design for this should be a number of small tables - Employees, PayPeriods, TimeSheets, PayTransactions, etc. Can you please tell us about the structure of these two tables you have, so that we can better understand what you are trying to do? -- TIA :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... OK. Let me explain further. The first table where data is entered, before the calculations are done has an ID, which is an autonumber and primary key. After I run my queries to calculate pay etc, the data is stored in a next table, using the same ID number form the 1st Table. I set the ID in that table to be primary key so that it does not duplicate, but that is set to number, not auto number, and it is into that table I would be keying the old data. It would be the first table I want to start the auto number at a high number, so that I can enter into the second table, past pay rolls, without duplicating the ID number. I hope you understand. Thanks "Graham Mandeno" wrote: Hi Christina We're not trying to be difficult here. Sure, it's possible to set your autonumber "seed" to start at 7500. Then you will get 7501, 7502, etc. But what happens when you want to go back and enter the old data? You won't be able to enter number 7499, and even if you were to try, you risk seriously corrupting your data. As Jeff put it, autonumbers are just a means for the database to uniquely identify a record, and they are not intended for human consumption. If your data has some natural way of being ordered, then you should add that value as a field. It sounds like your data needs to be ordered by date, so add a field named TransactionDate (or PayDate, or whatever) to store the date of the record. Then it doesn't matter what order they are entered, you will always be able to order the records by date. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... Thanks for the replies. I have a database that calculates payroll and stores the data to run year end reports for taxes. ( This is not in the US.) So if a person starts using the database during the year, the previous records need to be entered, so that the data is complete at year end. I have a unique identifier, for the new records which is an auto number, and the primary key. I would like to set the number to start at say 7500. My knowledge is very basic, so I would need detail instructions on what to do. Sorry, I mean very basic, on what to do. thanks "Douglas J. Steele" wrote: While Jeff's correct that you shouldn't care about the value of AutoNumber fields, I know that sometimes you have existing data, so you have to start at a higher number. See what Allen Browne has at http://www.allenbrowne.com/ser-26.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
#10
|
|||
|
|||
Auto Number
So basically, I would not be entering the old data in the table with the
AUTONUMBER. "Graham Mandeno" wrote: Hi Christina I hope you understand. I'm afraid I *don't* understand... Firstly I can't see the requirement for two parallel tables. Why can't the data for the past payrolls be stored in the same table as the first data? Second, the order of an autonumber, by definition, should never be important. In fact, often an autonumber field is set to generate random numbers! Surely when you enter some payroll information there is some "natural" ordering field - a date or a week number or a pay period number or ... Thirdly, it sounds like you are storing all your payroll data in a single table, like a big spreadsheet. A relational database is NOT a spreadsheet! The best design for this should be a number of small tables - Employees, PayPeriods, TimeSheets, PayTransactions, etc. Can you please tell us about the structure of these two tables you have, so that we can better understand what you are trying to do? -- TIA :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... OK. Let me explain further. The first table where data is entered, before the calculations are done has an ID, which is an autonumber and primary key. After I run my queries to calculate pay etc, the data is stored in a next table, using the same ID number form the 1st Table. I set the ID in that table to be primary key so that it does not duplicate, but that is set to number, not auto number, and it is into that table I would be keying the old data. It would be the first table I want to start the auto number at a high number, so that I can enter into the second table, past pay rolls, without duplicating the ID number. I hope you understand. Thanks "Graham Mandeno" wrote: Hi Christina We're not trying to be difficult here. Sure, it's possible to set your autonumber "seed" to start at 7500. Then you will get 7501, 7502, etc. But what happens when you want to go back and enter the old data? You won't be able to enter number 7499, and even if you were to try, you risk seriously corrupting your data. As Jeff put it, autonumbers are just a means for the database to uniquely identify a record, and they are not intended for human consumption. If your data has some natural way of being ordered, then you should add that value as a field. It sounds like your data needs to be ordered by date, so add a field named TransactionDate (or PayDate, or whatever) to store the date of the record. Then it doesn't matter what order they are entered, you will always be able to order the records by date. -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... Thanks for the replies. I have a database that calculates payroll and stores the data to run year end reports for taxes. ( This is not in the US.) So if a person starts using the database during the year, the previous records need to be entered, so that the data is complete at year end. I have a unique identifier, for the new records which is an auto number, and the primary key. I would like to set the number to start at say 7500. My knowledge is very basic, so I would need detail instructions on what to do. Sorry, I mean very basic, on what to do. thanks "Douglas J. Steele" wrote: While Jeff's correct that you shouldn't care about the value of AutoNumber fields, I know that sometimes you have existing data, so you have to start at a higher number. See what Allen Browne has at http://www.allenbrowne.com/ser-26.html -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Christina" wrote in message ... Is it possible to set an auto number in a table , which is the primary key, to start with a defined number say...1000. Thanks |
|
Thread Tools | |
Display Modes | |
|
|