Auto Number

[Christina]

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

 

[Jeff Boyce]

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

 

[Douglas J. Steele]

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

 

[Christina]

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

 

[Christina]

I looked at the link. Please help. Where should I post the code.

Thanks

 

[Graham Mandeno]

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.

 

[Christina]

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

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

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

 

[Graham Mandeno]

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

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

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

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

:

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

(no private e-mails, please)


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

 

[Christina]

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 .

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

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

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

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

:

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

(no private e-mails, please)


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

 

[Christina]

So basically, I would not be entering the old data in the table with the
AUTONUMBER.

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

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

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

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

:

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

(no private e-mails, please)


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

 

[Graham Mandeno]

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

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 .

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

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
:

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

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

:

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

(no private e-mails, please)


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

 

[Christina]

Thank you so much. It was so simple.

So basically, I would not be entering the old data in the table with the
AUTONUMBER.

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

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
:

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

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

:

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

(no private e-mails, please)


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

 

[Graham Mandeno]

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

Thank you so much. It was so simple.

So basically, I would not be entering the old data in the table with the
AUTONUMBER.

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



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
:

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

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

:

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

(no private e-mails, please)


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