Hi, Ruby.
upon a starting number I give it. If I could define a primary key field with
an autonumber I could use this field to increment from my starting number.
The problem is the only fields I can define are the ones that come from
existing tables and queries. i don't see how to define a field as a primary
key auto numbering field.
You'll need more than one query. In fact, you'll need eight queries, unless
you want to make manual changes to the table. Here are the steps:
1.) In your make-table query, alter the name of the table created to tblTemp.
2.) Run the make-table query.
3.) Create a new query and paste the following into the SQL View pane:
ALTER TABLE tblTemp
ADD COLUMN ID COUNTER (1001, 1)
CONSTRAINT PrimaryKey PRIMARY KEY;
Change the column name from ID to whatever name you want your primary key to
have, and change 1001 (the seed), to whatever number you want your invoices
to start at. Save and run the query. Open the tblTemp table and you'll
notice that Jet ignored your seed number. It doesn't matter what number you
use as the seed, because Jet will ignore it and start sequential numbering at
1. If this one thing worked correctly, then you wouldn't have to do any more
steps. But it doesn't.
You might be tempted to use an UPDATE query to add 1000 to all the numbers
in the primary key column to set them to the correct numbers, but you'll find
that this field is not updateable. But that doesn't mean that you can't
change these numbers.
4.) Create a new query and paste the following into the SQL View pane:
ALTER TABLE tblTemp
ALTER COLUMN ID LONG;
Change the column name from ID to the name of your primary key. Save and
run the query.
5.) Create a new query and paste the following into the SQL View pane:
UPDATE tblTemp
SET ID = ID + 1000;
Change the column name from ID to the name of your primary key and change
1000 to a number one less than the starting number for your invoices. Save
and run the query. Open the table and you'll find that the numbers are as
they should be, but the primary key column is of Long data type, not an
AutoNumber. You might be tempted to create and run a query that changes the
primary key column to an AutoNumber, but you can't change a field to an
AutoNumber when there are records in the table. But that doesn't mean that
you can't have an AutoNumber for this field.
5.) Create a new query and paste the following into the SQL View pane:
SELECT * INTO tblMyTable
FROM tblTemp
WHERE (1 = 0);
Change tblMyTable to the name of the table that your original make-table
query created. Save and run the query. You'll notice that this creates a
new table with no records, but the structure of the table is the same as
tblTemp. Because there are no records in this table, the primary key column
can be assigned an AutoNumber.
6.) Create a new query and paste the following into the SQL View pane:
ALTER TABLE tblMyTable
ALTER COLUMN ID COUNTER
CONSTRAINT PrimaryKey PRIMARY KEY;
.. . . where tblMyTable is the name of the table your original make-table
query creates, and ID is the name of the primary key column. Save and run
the query.
7.) Create a new query and paste the following into the SQL View pane:
INSERT INTO tblMyTable
SELECT *
FROM tblTemp
ORDER BY ID;
.. . . where tblMyTable is the name of the table your original make-table
query creates, and ID is the name of the primary key column. Save and run
the query. Voila! You have the table of records you should have had in the
first place. But there's some more administrative work to do.
8.) Create a new query and paste the following into the SQL View pane:
DROP TABLE tblTemp;
Save and run the query, then compact the database.
While these steps aren't the most efficient method of creating a new table
and assigning a primary key, this technique works. It also shows you the
technique to create a single, empty table of the correct structure, add a
primary key, append a record for the proper seed value, and delete that
record. Okay. It doesn't show how to append a record or delete that record,
but I suspect you could do that in your sleep. ;-) Then you could change
your original make-table query into an append query and append these records
into the new table. I'll leave this exercise up to you, but if you have
trouble with queries, then stick with the steps I've outlined above.
And remember that AutoNumbers aren't guaranteed to be sequential. While the
numbers for these records appended into the final table will be sequential,
usage of this table will result in gaps in the numbers, so if you need these
numbers to be sequential, then don't use an AutoNumber data type.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.
