Add autonumber key while creating a new table thru MakeTable Query

[n2nsj]

I need to check a large amount of sequential data for duplicate items,
missing items,
start and end of sequences, and other anomalies. The approach I am using is
to create a query that is sorted in asecnding order, ther using a make table
query to create a new table in the sorted order. I need to add an autonumber
primary key to the new table while it is being created. I know I can add it
after the fact, but that is too akward .
How can I add a sequential primary key to the table either during the
creation or with a single click?

 

[MGFoster]

I need to check a large amount of sequential data for duplicate items,
missing items,
start and end of sequences, and other anomalies. The approach I am using is
to create a query that is sorted in asecnding order, ther using a make table
query to create a new table in the sorted order. I need to add an autonumber
primary key to the new table while it is being created. I know I can add it
after the fact, but that is too akward .
How can I add a sequential primary key to the table either during the
creation or with a single click?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't create an AutoNumber column in a make-table query. You have
to run a DDL statement that will create the column (put these DDL
statements in a query's SQL view or run from VBA w/ the
CurrentDb.Execute method):

ALTER TABLE <table name> ADD COLUMN <column_name> COUNTER

COUNTER is the DDL data type for AutoNumber in JET (Access SQL).

Substitute the just made table name for <table name> and the desired
name of the AutoNumber column for <column_name>.

To create the PK you have to run another DDL statement:

ALTER TABLE <table name> ADD CONSTRAINT <constraint name>
PRIMARY KEY (<column list>)

E.g.:

ALTER TABLE Accounts ADD CONSTRAINT PK_Accounts
PRIMARY KEY (Account_ID)

The <column list> can contain one, or more, comma-separated column names
if you have more than one column that makes up the Primary Key.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyiyLYechKqOuFEgEQIimQCg+/bQFq1qd4wzM6W8z08oDTD2WZMAoMGN
KpB78m/6rnXNf2wUvgqIzZIM
=vNJf
-----END PGP SIGNATURE-----

 

[John Vinson]

I need to check a large amount of sequential data for duplicate items,
missing items,
start and end of sequences, and other anomalies. The approach I am using is
to create a query that is sorted in asecnding order, ther using a make table
query to create a new table in the sorted order. I need to add an autonumber
primary key to the new table while it is being created. I know I can add it
after the fact, but that is too akward .
How can I add a sequential primary key to the table either during the
creation or with a single click?

You can't.

And it's not necessary nor appropriate to create a new table to
accomplish what you're attempting. You're assuming that you can
"create a new table in the sorted order" - but you CAN'T; a table HAS
no order! It's an unordered "heap".

If you want to see records in order, use a Query sorting the records
in that order.

If you MUST have an autonumbered new table, create the new table,
empty, perhaps by copying the table in the tables window, choosing the
option "design mode only". Open the new table in design mode, add the
Autonumber field. Then run an Append query to append all the fields
except the autonumber.

John W. Vinson[MVP]

 

[peregenem]

You can't.

And it's not necessary nor appropriate to create a new table to
accomplish what you're attempting. You're assuming that you can
"create a new table in the sorted order" - but you CAN'T; a table HAS
no order! It's an unordered "heap".

I think sorted order is a red herring here

Here's a quick demo:

CREATE TABLE MyHeap (
data_col VARCHAR(10) NOT NULL
);
INSERT INTO MyHeap VALUES ('B');
INSERT INTO MyHeap VALUES ('D');
INSERT INTO MyHeap VALUES ('C');
INSERT INTO MyHeap VALUES ('A');

SELECT (
SELECT COUNT(*)
FROM MyHeap
WHERE data_col <= T1.data_col
) AS key_col,
data_col
INTO MyTable
FROM MyHeap AS T1;

The resulting data in MyTable is not physically in order of key col but
key_col doesn give the 'logical' order and the data could be sorted by
the new key_col if required e.g. in a recordset. Subsequently creating
a primary key on key_col *would* effectively be a single click. For
this technique to work, a natural unique key must already exist so the
incrementing integer would probably not be the best choice for PK ...

 

[n2nsj]

I think sorted order is a red herring here

Here's a quick demo:

CREATE TABLE MyHeap (
data_col VARCHAR(10) NOT NULL
);
INSERT INTO MyHeap VALUES ('B');
INSERT INTO MyHeap VALUES ('D');
INSERT INTO MyHeap VALUES ('C');
INSERT INTO MyHeap VALUES ('A');

SELECT (
SELECT COUNT(*)
FROM MyHeap
WHERE data_col <= T1.data_col
) AS key_col,
data_col
INTO MyTable
FROM MyHeap AS T1;

The resulting data in MyTable is not physically in order of key col but
key_col doesn give the 'logical' order and the data could be sorted by
the new key_col if required e.g. in a recordset. Subsequently creating
a primary key on key_col *would* effectively be a single click. For
this technique to work, a natural unique key must already exist so the
incrementing integer would probably not be the best choice for PK ...

Please let me expand of my problem.

I need to compare sequenial records in the same table. Threre is a natural
primary key in the data but the data does not arrive in that sequence. the
problem becomes placing the data in sequence (I use a acending sort in the
first query), then comparing each record in the sorted order to the next
record in sort order and finding the start and end of each subsequence,
missing data from within the subsequence, and other features such as last
record of each sub sequence, and date ranges.
The methology I used was to build two tables(TableOne and TableTwo), the
first sorted into natural key order and then add an autonumber key to create
a continuious key(TableOne), then a create a second table identical to the
first except that the key is incresed by one(TableTwo). I link the two tables
by the keys and now can combine the two sequencial records into one and
compare the data within.
All this is done thru normal queries and make table queries except for
adding the first sequential key to TableOne.

When I am done I need to teach others with much less computer savy to do
these procedures. To click on a macro or query name is easy, to add a primary
key is not.

I am not at all proficent in SQL or VBA and was trying to use only the
commands directly avalible thru access, however I am willing to learn and try
all sugestions.

Is there another approch that may be easier to solve my problem?

 

[peregenem]

I need to compare sequenial records in the same table. Threre is a natural
primary key in the data but the data does not arrive in that sequence.

Take a look at my earlier example: that data 'does not arrive' or end
up in natural key order, but the relative order is still derived.

The methology I used was to build two tables(TableOne and TableTwo), the
first sorted into natural key order and then add an autonumber key to create
a continuious key(TableOne), then a create a second table identical to the
first except that the key is incresed by one(TableTwo).
I am not at all proficent in SQL

I think your methodology required reorienting You are currently
thinking 'procedurally'; you need to be thinking in terms of sets. I
don't need to sort a data set by date order or assign it a sequence
number to determine *relative* date order i.e. whether one date is
greater than another etc. This can be extended to more than one column
to determine relative order, missing sequence values (easier if you use
a sequence table e.g. calendar table), data duplicated/missing in a
second set, etc. And at not point is an ORDER BY or autonumber
required. I suggest you post a sample of your data with your expected
results.