appending records and primary key issues

  • Thread starter Thread starter Kamal Hood
  • Start date Start date
K

Kamal Hood

Hi,
I'd like to append records from a secondary table to my main table
periodically. I'd like the appended records to automatically recieve new
primary key values (numbers) consecutively. I don't care about or want to
save the values of the key field in the secondary table into my main table
when appending the records. I'd just like to copy over all the other fields
that match except for the key field and then have these items given a new
primary key number in the main table starting with 1 larger than the largest
key in the main table.

i.e. if my main table has these records in it:
KeyID Name
1 Aaaa
2 Bbbb
3 Cccc


and my second table has these fields in it:
KeyID Name
342 Dddd
5123 Eeeee


Then after appending i'd like the main table to look like this:
KeyID Name
1 Aaaa
2 Bbbb
3 Cccc
4 Dddd
5 Eeeee

I've read stuff about stored procedures being able to do this but I'm not
using sql server nor an access project; just a regular .mdb file.

can anybody help?

thanks,
kamal
 
I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.
 
thanks for the response joseph, but where did you get Autonumbers from? I'm
not using Autonumbers for this field at all. It is a primary key but not an
autonumber. i just don't know how to assign a field consecutive, unique
numbers when appending records.

if you can help out i'd appreciate it though. you say there's ways of
providing the numbers I want. that's the stuff i need to know how to do. do
you know of any good websites to get this info?

thx,
kamal
 
Autonumber is a field type. Fields can be number (in several flavors),
text, memo, currency, yes-no, date-time, others and autonumber.

I don't have any self numbering code handy as I have never had the need,
but if someone is going to help you this way, then they can do a better job
if they know exactly why you need the numbers and how they may be changed
etc.

I know Access likes to have a key field and likes it to be numbers, but
there are many times there is no need for a key field. If the table is not
linked, you may not need a KeyID field. If it is linked and that is the
reason for the number field, then autonumber is the tool you want.
 
Back
Top