Append Table - Auto Number Problem

[Guest]

I have a query/append table process that takes historical data, ascending by
date and adds new unique records as they appear (the data eventually gets
removed from the original database source I query in approx. six months due
to an IT archive process). The problem is the append table auto numbering
tries to renumber the existing records before it assigns a number to the new
unique records.

For example, the existing append table has 100 records, you would think the
next unique record added would be 101 but it's not. The auto number tries to
renumber the existing 100 records and when it sees the initial unique 100
records exist, it "cancels" the auto number assignment - which it still sees
as that series of numbers as being used/assigned (thinks 101 to 200 have been
assigned even though they haven't) and it assigns 201 to the next new record.

I have to keep the data in the append table in it's current ascending by
date order, the records cannot be duplicated (primary key applied) and the
"auto" numbering must begin with #1 and ascend in order without any gaps.

Any insight on how this could be accomplished would be greatly appreciated.

Thank you in advance for your time and consideration.

Regards,

Mike

 

[Guest]

The best thing is to NOT remove the records but add a field named something
like Archive. Then flag those records you would normally remove and in all
your queries add criteria to pull the records not archived.

 

[Pat Hartman\(MVP\)]

You can use an unmatched query to eliminate the duplicate records so that
Access only appends the new records. Use the query builder wizard to build
the unmatched query. Then change it to an append query.

BTW - the Autonumber's primary use is as a unique identifier. It cannot be
used as a sequence number because there are legitimate reasons for gaps to
occur in the number assignment. It's only other use is to order a recordset
by data entry order.

If you need a sequence number, you'll have to create one yourself. You can
use something like:

Nz(DMax("yourkeyfield", "yourtable"),0) + 1

 

[Guest]

Hi, I have the same problem with the append query but you can actually go to
the properties and change the setting to get rid of duplications when you
append. Pat, The code you wrote Nz(DMax("yourkeyfield", "yourtable"),0) + 1
where do you put the code at, is it on the criteria field in design view
(query) of the reference numbering column only? Also I would like to know
if it's possible to prevent users to save the query file if they change the
selection of their table heading. Because when you deselect a certain table
heading and run the query and go back to design view only the selected
heading show and not all the database colomn heading. Please help.