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 |
#11
|
|||
|
|||
Auto Number
Hi Christina
Well... it's a *little* clearer :-) It seems to me that the only reason for copying the autonumber value in the first table to the second is to ensure that records from the first table don't accidentally get inserted twice. Is that right? In that case, records that are entered manually have no need for any value in that field at all. The field should NOT be a primary key, should not be "Required", and should be indexed allowing no duplicates. Does this make sense? -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... 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 |
#12
|
|||
|
|||
Auto Number
Thank you so much. It was so simple.
"Christina" wrote: 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 |
#13
|
|||
|
|||
Auto Number
You're welcome! It's funny how often we can't see the woods for the trees
:-) -- Good Luck :-) Graham Mandeno [Access MVP] Auckland, New Zealand "Christina" wrote in message ... Thank you so much. It was so simple. "Christina" wrote: 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 | |
|
|