Query that will assign a sequence number

[Vika]

Is it possible to create a query that will assign a sequence number t
the records and append it to the table
I need a query that compares records from table 1 to table 2. If ther
are new records in table 1, the query has to look up the last sequenc
number in the table 1, then assign a sequence number to new record
and append it to the table 2

Here is what I have tried

1. Created a query that matches records in table 1 and table
2. Created a query that appends sequential number from table 1 t
matching records in table
3. Created a qryMaxSeqNO query that uses a DMax function to look u
max sequence number in table
4. Created a qryRunningSeqNO query from qryMaxSeqNo query that uses
function RunningTotal:[MaxSeqNO]+
5. Created a query that assigns a sequential number to the new record
using table 1(where SeqNo is Null) and qryRunningSeqNO query
(Fields:RunningTotal(from qryRunningSeqNO), and all fields from th
table 1
The problem is that the sequence number that query assigns to the ne
records is not sequential. It looks up for maximum sequence number i
the table 1, adds 1 and then assigns the same number to all the ne
records that are found in table 2. For example, if the maximu
sequence number in table 1 is 450; all the new found records in tabl
2 will have a sequence number 451.

Thank you very much for any help

Vik

 

[Chris2]

Is it possible to create a query that will assign a sequence number to
the records and append it to the table?
I need a query that compares records from table 1 to table 2. If there
are new records in table 1, the query has to look up the last sequence
number in the table 1, then assign a sequence number to new records
and append it to the table 2.

Here is what I have tried:

1. Created a query that matches records in table 1 and table 2
2. Created a query that appends sequential number from table 1 to
matching records in table 2
3. Created a qryMaxSeqNO query that uses a DMax function to look up
max sequence number in table 1
4. Created a qryRunningSeqNO query from qryMaxSeqNo query that uses a
function RunningTotal:[MaxSeqNO]+1
5. Created a query that assigns a sequential number to the new records
using table 1(where SeqNo is Null) and qryRunningSeqNO query.
(Fields:RunningTotal(from qryRunningSeqNO), and all fields from the
table 1)
The problem is that the sequence number that query assigns to the new
records is not sequential. It looks up for maximum sequence number in
the table 1, adds 1 and then assigns the same number to all the new
records that are found in table 2. For example, if the maximum
sequence number in table 1 is 450; all the new found records in table
2 will have a sequence number 451.

Thank you very much for any help.

Vika

Vika,

Tables:

CREATE TABLE Unknown_10312005_1
(UnknownID AUTOINCREMENT
,TestValue TEXT(1)
,CONSTRAINT pk_Unknown_10312005_1 PRIMARY KEY (UnknownID)
)

CREATE TABLE Unknown_10312005_2
(UnknownID AUTOINCREMENT
,TestValue TEXT(1)
,CONSTRAINT pk_Unknown_10312005_2 PRIMARY KEY (UnknownID)
)

Sample Data:

Unknown_10312005_1
1, a
2, b
3, c

Unknown_10312005_2
1, d
2, e
3, a


Expectations:

Rows with UnknownID values 1 and 2 from Unknown_10312005_2, with
TestValues not found in Unknown_10312005_1, should be INSERTed into
Unknown_10312005_1 with new UnknownID values 5 and 6.


Query:

The SELECT portion of the INSERT below grabs the MAX UnknownID from
Unknown #1, and then adds the current rowcount for Unknown #2 via
correlation.

INSERT INTO Unknown_10312005_1
(UnknownID
,TestValue
)
SELECT ((SELECT MAX(U11.UnknownID)
FROM Unknown_10312005_1 AS U11)
+ (SELECT COUNT(U02.UnknownID)
FROM Unknown_10312005_1 AS U02
WHERE U02.UnknownID <= U2.UnknownID))
,U2.TestValue
FROM Unknown_10312005_2 AS U2
WHERE NOT EXISTS
(SELECT *
FROM Unknown_10312005_1 AS U01
WHERE U01.TestValue = U2.TestValue)

Output:

Unknown_10312005_1
1, a
2, b
3, c
4, d
5, e

Backup your data before trying it.


Sincerely,

Chris O.