Hi, David.
In addition to what Graham wrote, Jet SQL doesn't support Sequences as
Oracle does, since it doesn't support triggers. One can create a somewhat
equivalent value by setting the desired sequence column as an AutoNumber
data type, which automatically increments to the next value and stores this
value in the AutoNumber column whenever a new record is inserted.
The limitations of an AutoNumber are that the numbers aren't guaranteed to
be sequential, and that since the underlying data type is Long, only 4.3
billion numbers are guaranteed to be unique before rolling over to the
beginning at 1. Fortunately, file space limitations usually prevent this
from happening.
If your data needs a column with guaranteed sequential numbers (i.e., has
some meaning, other than serving as a surrogate key), then you'll need to
create a user-defined function to create a sequential number for each
record.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are
[email protected] and
[email protected]
David,
Appending TableB's data to TableA is simple:
INSERT INTO TableA SELECT TableB.A1, TableB.B1 FROM TableB
Creating a sequence in a table is unnecessary, and meaningless, because
tables are relatively unordered. You can create any ordering you want when
you query the table:
SELECT A1, B1 FROM TableA
ORDER BY B1
Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
I have two tables: table A (fields: A1, B1) and table B (fields: Seq, A1,
B1). I need to append table A's records into table B, and generate a
sequence in Seq of table (a,b,c.d.... or 101, 102, 103, 104...). How to
realize it with SQL?
